Parsing Pipe Files Into SQL

[moone009]

Using VB.NET 2010 All helps is greatly appreciated!

Currently I am parsing through 1,000s of text files each day and inserting them into SQL. I am unsure how to break up the actual pipe delimited data and insert it properly into my database columns so I am doing that through SQL (using some brute force scripts) but I would much rather do that through vb.net.

Example data:
Test|2324|34WASH|DOO323-03234|||Completed

Col1 = Test
Col2 = 2324
Col3 = 34WASH
Col4 = DOO323-03234
Col5 = ''
Col6 = ''
Col7 = Completed

        Dim di As New DirectoryInfo("c:\test")
        Dim fiArr As FileInfo() = di.GetFiles()
        Dim fri As FileInfo
        For Each fri In fiArr


            Dim FILE_NAME As String = "c:\test\" + fri.Name

            If System.IO.File.Exists(FILE_NAME) = True Then

                Dim objReader As New System.IO.StreamReader(FILE_NAME)

                Do While objReader.Peek() <> -1

                    '  Dim s As String = objReader.ReadLine().ToString()
                    Dim s As String = objReader.ReadLine.ToString()
                    Dim sa As String = fri.Name


                    Try
                        cmd.CommandText = " insert into dbo.TestImport values(" & "'" & s & "'" & ",'" & File.GetLastWriteTime(FILE_NAME).ToString & "','" & sa & "'" & ")"
                        cmd.ExecuteNonQuery()


                    Catch ex As Exception
                        cmd.CommandText = " insert into dbo.TestImport values(" & "'" & "unclosed mark" & "'" & ",'" & File.GetLastWriteTime(FILE_NAME).ToString & "','" & sa & "'" & ")"
                        cmd.ExecuteNonQuery()

                    End Try


                Loop

            End If

        Next fri

[Reverend Jim]

I'm not up on oledb. I pretty much stick with ADO so my example uses that. You have to split the input line into fields. You do that with (what a coincidence) the Split method

        Dim sr As New StreamReader("d:\temp\test.txt")
        Dim con As New ADODB.Connection
        Dim qry As String

        con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")

        Do Until sr.EndOfStream

            Dim line As String = sr.ReadLine
            Dim cols() As String = line.Split("|")

            qry = "INSERT INTO TestImport (Col1,Col2,Col3,Col4,Col5,Col6,Col7) Values(" & _
                "'" & cols(0) & "'," & _
                "'" & cols(1) & "'," & _
                "'" & cols(2) & "'," & _
                "'" & cols(3) & "'," & _
                "'" & cols(4) & "'," & _
                "'" & cols(5) & "'," & _
                "'" & cols(6) & "')"

            con.Execute(qry)

        Loop

        sr.Close()
        con.Close()

"Split" allows you to specify the character that separates the fields. I took the liberty of assuming that all fields are varchar. You do not need the single quotes around numeric fields. Also, for this example, I don't use a parameterized query which leaves the app open to a SQL injection attack unless you trust, or have verified your input data.