SQL to text Learn programming Visual Basic (VB.net)



Lesson:

File Management


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



Juan A. Ripoll - Systems Tutorials and Programming Courses ©  All rights reserved.  Legal Conditions.