Here is an example
14 B2900 1 50 0 0
14 B2900 51 100 0 0
14 B2900 101 150 0 0
14 B2900 151 200 0 0
14 B2900 201 250 0 0
14 B2900 251 300 0 0
14 B2900 301 350 0 0
14 B2900 351 400 0 0
14 B2900 401 450 0 0
14 B2900 451 500 0 0
14 B2900 501 550 0 0
14 B2900 551 600 0 0
14 B2900 601 650 46 2
14 B2900 651 700 50 16
14 B2900 701 750 54 31
14 B2900 751 800 59 45
14 B2900 801 850 71 60
14 B2900 851 900 85 74
14 B2900 901 950 102 89
14 B2900 951 1000 117 103
14 B2900 1001 1050 131 118
14 B2900 1051 1100 146 132
14 B2900 1101 1150 163 147
14 B2900 1151 1200 178 161
14 B2900 1201 1250 192 176
14 B2900 1251 1300 207 190
16 500 rows takes about 35 sec. to insert to the database..
“is there” a better and faster way to insert this text file to the Ms Access database.
and how should do to make the code better.
here are the complete code i use now.
'Create an StreamReder object
Dim objSr As New StreamReader(Application.StartupPath & "\04.txt")
Dim line, vals() As String
DBCon = New OleDbConnection(ConnectionString)
Dim cm As OleDbCommand
Try
'Open connection
If DBCon.State = ConnectionState.Closed Then
DBCon.Open()
End If
Catch Exc As Exception
'An error occurred, notify the user and exit this routine.
MessageBox.Show(Exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
DBCon.Dispose()
Exit Function
End Try
ReDim vals(6)
Do
line = objSr.ReadLine
If line Is Nothing Then Exit Do
'Place values in array
vals(0) = Trim(line.Substring(3, 5))
vals(1) = Trim(line.Substring(8, 6))
vals(2) = Trim(line.Substring(14, 6))
vals(3) = Trim(line.Substring(20, 6))
vals(4) = Trim(line.Substring(26, 6))
cm = New OleDbCommand("INSERT INTO T1 (temp1, temp2, temp3, temp4, temp5) VALUES (" & "'" & vals(0) & "','" & vals(1) & "','" & vals(2) & "','" & vals(3) & "','" & vals(4) & "')", DBCon)
Try
cm.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Loop
'Close connection
If DBCon.State = ConnectionState.Open Then
DBCon.Close()
DBCon.Dispose()
End If
'Dispose of all your objects
cm = Nothing
objSr.Close()
objSr = Nothing
MessageBox.Show(".....", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information)