Can anyone help me with importing text file to sql server using vb.net

here is my code

Dim ImportedFile As String

Dim ImportedLines() As String
Dim OneLine() As String
Dim strSQL As String
Dim i As Integer
If My.Computer.FileSystem.FileExists("D:\PPOS\Sales_report\inventory_255503221452_Cake102.CSV") Then
' Read the whole text file
' Separate lines
ImportedLines = Strings.Split(ImportedFile, Environment.NewLine)
For i = 0 To ImportedLines.GetUpperBound(0)
' Separate fields
OneLine = Strings.Split(ImportedLines(i), ",")
If OneLine.GetUpperBound(0) = 8 Then
' Build SQL insert statement
strSQL = "INSERT INTO inventory(Inventory_id,item_id,Amount,AvgCost,CreateDate,CreateUser,Real_Qty,UnitID,BranchID) VALUES (" & OneLine(0) & ", " & OneLine(1) & ", " & OneLine(2) & ", " & OneLine(3) & ", '" & objgen.ChgDate(CDate(OneLine(4).ToString.Replace("'", "")), 0) & "', " & OneLine(5) & ", " & OneLine(6) & ", " & OneLine(7) & ", " & OneLine(8) & ")"
objacc.ExecNonquery(strSQL)
Else
MessageBox.Show("Wrong number of fields", "Notification", MessageBoxButtons.OK)
' Error: Wrong number of fields
End If
Next i
Else
End If
End Sub


What happen is the For loop got excecuted 3 times more than the actual lines i have got in the text file

here is the data contains in the file

9,15,4440,60,'29/08/2553 11:49:10','Admin',72,21,'Cake101'


Every comment appreciated.
Thanks.

Edited by pyTony: data file format

4
Contributors
4
Replies
15
Views
5 Years
Discussion Span
Last Post by ExpertMind

You can do it this way:

Call this method:

Private Sub InsertingIntoDB()
Dim fileName As String = "D:\PPOS\Sales_report\inventory_255503221452_Cake102.csv"
Using conn As New SqlConnection("connString")
Dim query As String = "INSERT INTO inventory (Inventory_id, item_id, Amount, AvgCost, CreateDate, CreateUser, Real_Qty, UnitID, BranchID) " & "VALUES(@a, @b, @c, @d, @e, @f, @g, @h, @i)"
Using cmd As New SqlCommand(query, conn)
For Each line As String In ReadingFile(fileName)
Dim data As String() = line.Split(New Char() {","C}, StringSplitOptions.RemoveEmptyEntries)
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
Exit Try
End Try
Next
End Using
End Using
End Sub

Private Function ReadingFile(path As String) As IEnumerable(Of String)
Dim line As String
yield Return line
End While
End Using
End Function


I dont know exact types of your columns, so check them out.
hope it helps,
bye

I just found out that the problem is not with the importing procedure but the actual problem is when i'm exporting the file

But still i don't get it why it is always have 3 lines more than the actual line that i got in the database.

You may be able to use the bulk insert capability. Here ase several links to more information

I used to do this when speed was a priority. I had to import 8000+ records every five minutes and it all had to be done as one transaction. Doing it record by record in a loop was not an option. If the text file is not in the correct format then you could always massage it into a suitable form (and validate the data) prior to bulk inserting.

Hi,

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.