SQL to text

Lesson:

File Management

Exercise:

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