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