By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,230 Members | 1,320 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,230 IT Pros & Developers. It's quick & easy.

read from Text file using VB and Insert into SQL table until eof text file reach

P: n/a
Dear All,

my text file like following,

a1, b1, c1, d1, e1
a2, b2, c2, d2, e2
a3, b3, c3, d3, e3
a4, b4, c4, d4, e4

and im going to insert this values into SQL Tables with columns, [a, b, c, d,

how im going to do so? i use below src code to do but i can not get the next
line and next line of text file until it finished insert

Private Sub mot_Click()
'Shell ("C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE c:\fisherdb.
mdb /x ImportOpsNilai")

Dim db_connect
' Path = App.Path & "\ERP consumable code.xls"

Dim objFSO
Dim objFile, objTextStream
Dim strContent
Const ForReading = 1
Dim FileName, IEPath, strC1 As String
FileName = "U:\bureport\buops\RepNilai.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.GetFile(FileName)

Set objTextStream = objFile.OpenAsTextStream(ForReading,

If objFSO.FileExists(FileName) Then

Set objFile = objFSO.OpenTextFile(FileName, ForReading)

'While Not objFile.EOF
strC1 = objFile.ReadLine
Dim word
ReDim word(2)
word = Split(strC1, ",")
' Dim t1, t2, t3 As String
' MsgBox RTrim(LTrim(word(44)))
' t1 = Replace(word(0), """", "")
' t2 = Replace(word(1), """", "")
Dim sql
Dim rs As New ADODB.Recordset
sqldb.Execute ("delete from OPS_Nilai")
rs.Open ("Select * from OPS_Nilai"), sqldb, adOpenDynamic,
With rs
!Field1 = RTrim(LTrim(word(0)))
!Field2 = RTrim(LTrim(word(1)))
!Field3 = RTrim(LTrim(word(2)))
!Field4 = RTrim(LTrim(word(3)))
!Field5 = RTrim(LTrim(word(4)))
!Field6 = RTrim(LTrim(word(5)))
!Field7 = RTrim(LTrim(word(6)))
!Field8 = RTrim(LTrim(word(7)))
!Field9 = RTrim(LTrim(word(8)))
!Field10 = RTrim(LTrim(word(9)))
!Field11 = RTrim(LTrim(word(10)))
!Field12 = RTrim(LTrim(word(11)))
!Field13 = RTrim(LTrim(word(12)))
!Field14 = RTrim(LTrim(word(13)))
!Field15 = RTrim(LTrim(word(14)))
!Field16 = RTrim(LTrim(word(15)))
!Field17 = RTrim(LTrim(word(16)))
!Field18 = RTrim(LTrim(word(17)))
!Field19 = RTrim(LTrim(word(18)))
!Field20 = RTrim(LTrim(word(19)))

!Field21 = RTrim(LTrim(word(20)))
!Field22 = RTrim(LTrim(word(21)))
!Field23 = RTrim(LTrim(word(22)))
!Field24 = RTrim(LTrim(word(23)))
!Field25 = RTrim(LTrim(word(24)))
!Field26 = RTrim(LTrim(word(25)))
!Field27 = RTrim(LTrim(word(26)))
!Field28 = RTrim(LTrim(word(27)))
!Field29 = RTrim(LTrim(word(28)))
!Field30 = RTrim(LTrim(word(29)))
!Field31 = RTrim(LTrim(word(30)))
!Field32 = RTrim(LTrim(word(31)))
!Field33 = RTrim(LTrim(word(32)))
!Field34 = RTrim(LTrim(word(33)))
!Field35 = RTrim(LTrim(word(34)))
!Field36 = RTrim(LTrim(word(35)))
!Field37 = RTrim(LTrim(word(36)))
!Field38 = RTrim(LTrim(word(37)))
!Field39 = RTrim(LTrim(word(38)))
!Field40 = RTrim(LTrim(word(39)))

!Field41 = RTrim(LTrim(word(40)))
!Field42 = RTrim(LTrim(word(41)))
!Field43 = RTrim(LTrim(word(42)))
!Field44 = RTrim(LTrim(word(43)))
!Field45 = RTrim(LTrim(word(44)))

End With
' Loop
End If

MsgBox "Update completed!!!", vbOKOnly + vbInformation, "Fisher Nilai"
End Sub

kindly advice
thank you

Message posted via

Jul 30 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.