Import CSV text file to MS Access DB

[MarkTingson]

Hello,

Can anyone here has a code for importing CSV text file to Access table??

thanks in advance..

Sorry Debasis Das for disturbing...

[VBWheaties]

Hello,

Can anyone here has a code for importing CSV text file to Access table??

thanks in advance..

Sorry Debasis Das for disturbing...

Direct from Microsoft:

http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb07/hey0206.mspx

[MarkTingson]

Direct from Microsoft:
http://www.microsoft.com/technet/scr...7/hey0206.mspx

Thanks sir. I'll give this a shot!

[MarkTingson]

i have a problem with this

Public Sub ImportToFile(sr cRS As String, srcFile As String)

Const adOpenStatic = 3
Const adLockOptimisti c = 3
Const ForReading = 1

Con_Open
Dim rsImport As ADODB.Recordset
Dim objFSO
Dim objFile
Dim strFile As String
Dim strArrFile() As String

Set rsImport = New ADODB.Recordset
'rsImport.Curso rLocation = adUseClient
rsImport.Open srcRS, con, adOpenStatic, adLockOptimisti c

Set objFSO = CreateObject("S cripting.FileSy stemObject")
Set objFile = objFSO.OpenText File(srcFile)

Do Until objFile.AtEndof Stream
strFile = objFile.ReadLin e
strArrFile = Split(strFile, ",")

With rsImport
.AddNew
rsImport("strOR num") = strArrFile(0)
.Fields("strIte mCode") = strArrFile(1)
.Fields("strIte mName") = strArrFile(2)
.Fields("intqty ") = strArrFile(3)
.Fields("intUni tPrice") = strArrFile(4)
.Fields("intAmo unt") = strArrFile(5)
.Fields("intDis count") = strArrFile(6)
.Fields("intCha rged") = strArrFile(7)
.Fields("intBra nchFK") = strArrFile(8)
.Fields("datOrd er") = CDate(strArrFil e(9))
.Update
End With
Loop



rsImport.Close

End Sub

It says, "Subscript out of Range."

What was that mean? I actually trying to make this a procedure that i can call globally.

Call ImportToFile("S ELECT * FROM tblDSR", App.Path & "\RECEivedfiles \MainDSR.txt")

I've already check my database fields and it is correct. Please help guys...

Thank you so much!

[MarkTingson]

Ahmm, guys i forgot to tell, the procedure is actually successful but my problem is, what does the "Subscript out of range" error means? and how can i get id of it.

Thanks again

[VBWheaties]

Ahmm, guys i forgot to tell, the procedure is actually successful but my problem is, what does the "Subscript out of range" error means? and how can i get id of it.

Thanks again

Subscript out of range error means an array index is invalid (there is no subscript index with that number).

From your code, it looks like one of the paranthesed values is the culprit. When you try to reference an array (like MyArray(4)) and the upper bound is 2, then you will get this error.

example:

Dim MyArray(2) As String 
Msgbox MyArray(4) & " would throw Subscript Out of Range because 4 does not exist."