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


File Management


SQL to text


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


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()

                ficheroEntrada = File.OpenText(nombre)
            Catch e As Exception
            End Try


                    linea = ficheroEntrada.ReadLine()

                    If linea IsNot Nothing Then
                        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)

                        For i As Integer = 0 To campo.Length - 1
                            Console.Write(campo(i).Trim() & ": ")
                            Console.WriteLine(valores(i).Trim().Replace("'", ""))
                    End If
                Loop While linea IsNot Nothing

            Catch e As Exception
            End Try

        End Sub
    End Class
End Namespace

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