TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
Reply
Answers (
0
)
making setup program for my c# software
Accessing return values(integer ) of stored procedure