spam burek

spam burek

  • NA
  • 1
  • 0

Import text file to DB

Jul 31 2006 4:55 AM
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