Exercise
SQL to text
Objetive
You must create a Visual Basic (VB.Net) program that is capable of parsing SQL INSERT commands and extracting their data into separate lines of text, as follows. If the input file contains these three lines:
INSERT INTO people (name, address, age) VALUES ("smith, pedro", "your street", 23);
INSERT INTO people (name, address, age) VALUES ("juan", "calle cinco, 6", 24);
INSERT INTO cities (code, name) VALUES ("a", "alicante");
The resulting file should have on each line the name of a field, followed by a colon and its value. In addition, each record must be preceded by the name of the table and followed by a blank line, like this:
Table: people
name: smith, pedro
address: your street
age: 23
Table: people
name: juan
address: calle cinco, 6
age: 24
Table: cities
code: a
name: alicante
Code
Imports System
Imports System.IO
Namespace SQL2text
Class Program
Private Shared Sub Main(ByVal args As String())
Dim ficheroEntrada As StreamReader = Nothing
Dim linea As String
Dim nombre As String
Console.WriteLine("Not enough parameters!")
Console.Write("Enter file name: ")
nombre = Console.ReadLine()
Try
ficheroEntrada = File.OpenText(nombre)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Try
Do
linea = ficheroEntrada.ReadLine()
If linea IsNot Nothing Then
Console.WriteLine()
Dim tableName As String = linea.Substring(12).Split(" "c)(0)
Dim campo As String() = linea.Substring(linea.IndexOf("(") + 1, linea.IndexOf(")") - linea.IndexOf("(") - 1).Split(","c)
Dim valores As String() = linea.Substring(linea.IndexOf("values (") + 9, linea.IndexOf(");") - linea.IndexOf("values (") - 9).Split(","c)
Console.WriteLine(tableName)
For i As Integer = 0 To campo.Length - 1
Console.Write(campo(i).Trim() & ": ")
Console.WriteLine(valores(i).Trim().Replace("'", ""))
Next
End If
Loop While linea IsNot Nothing
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Console.ReadLine()
End Sub
End Class
End Namespace