Hi,
At the bottom of this post you will find my code, which I use to import file to Access. Code itself is working but too slow.
What's my problem? I'd like to import file size 20MB, but the whole thing last about 8 min.
Is it possible to get shorter, with some different approach.
Thank you for your comments!
Gregor
******************************************
Public Sub ImportTXT_2(ByVal i_FileName As String)
Dim currentRow As String()
Dim currentField As String
Dim arrDPK(19) As String
Dim i As Integer
Dim MySQL As String
Dim sRead As Date
Dim eRead As Date
sRead = Now
If Not File.Exists(i_FileName) Then
MessageBox.Show(i_FileName & " does not exist!", i_FileName)
Return
End If
Dim sr As FileIO.TextFieldParser = New FileIO.TextFieldParser(i_FileName)
sr.TextFieldType = FileIO.FieldType.FixedWidth
sr.SetFieldWidths(4, 3, 5, 4, 3, 1, 12, 3, 4, 7, 5, 6, 13, 1, 6, 16, 13, 4, 2)
sr.TrimWhiteSpace = True
While Not sr.EndOfData
Try
currentRow = sr.ReadFields()
i = 0
For Each currentField In currentRow
arrDPK.SetValue(currentField, i)
i = i + 1
Next
MySQL = "INSERT INTO [tblDPK] (DEPT, CCY, CATEGORY, SECTOR, BORROW, " & _
"RES, ASSET_TYPE, TRANS_CODE, INIT_TERM, " & _
"CUST, SUB_ACC, POST_DATE, AMT, POST_SIGN, " & _
"VALUE_DATE, DOC_NUMBER, TOM_AMT, CUST_STAT, " & _
"PURPOSE) " & _
"VALUES('" & _
arrDPK.GetValue(0) & "','" & _
arrDPK.GetValue(1) & "'," & _
IIf(arrDPK.GetValue(2) = "", "Null", arrDPK.GetValue(2)) & "," & _
IIf(arrDPK.GetValue(3) = "", "Null", arrDPK.GetValue(3)) & ",'" & _
arrDPK.GetValue(4) & "','" & _
arrDPK.GetValue(5) & "','" & _
arrDPK.GetValue(6) & "','" & _
arrDPK.GetValue(7) & "','" & _
arrDPK.GetValue(8) & "','" & _
arrDPK.GetValue(9) & "','" & _
arrDPK.GetValue(10) & "','" & _
arrDPK.GetValue(11) & "','" & _
arrDPK.GetValue(12) & "','" & _
arrDPK.GetValue(13) & "','" & _
arrDPK.GetValue(14) & "','" & _
arrDPK.GetValue(15) & "'," & _
IIf(arrDPK.GetValue(16) = "", "Null", arrDPK.GetValue(16)) & ",'" & _
arrDPK.GetValue(17) & "','" & _
arrDPK.GetValue(18) & "')"
SQLCommand.CommandText = MySQL
SQLCommand.ExecuteNonQuery()
Catch ex As FileIO.MalformedLineException
MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
End Try
End While
eRead = Now
MessageBox.Show("Start: " & sRead & " - End: " & eRead)
End Sub