griffins69
griffins69
- Local time
- Yesterday, 21:10
- Joined
- Apr 15, 2010
- Messages
- 18
I'm using the below code to import records from a CSV file. I'm running into problems when I run into double quotes with a comma in the field.
This extra comma is causing another field to be created.
Dim F As Long, sLine As String, A(0 To 4) As String
Dim db As Database, rs As Recordset
F = FreeFile
Open "c:\temp\test.txt" For Input As F
Set db = CurrentDb
On Error Resume Next
On Error GoTo 0
Set rs = db.OpenRecordset("TestImport", dbOpenTable)
Do While Not EOF(F)
Line Input #F, sLine
ParseToArray sLine, A()
rs.AddNew
rs(0) = A(0)
rs(1) = A(1)
rs(2) = A(2)
rs(3) = A(3)
rs(4) = A(4)
rs.Update
Loop
rs.Close
db.Close
Close #F
End Sub
Sub ParseToArray(sLine As String, A() As String)
Dim P As Long, LastPos As Long, i As Long
P = InStr(sLine, ",")
Do While P
A(i) = Mid$(sLine, LastPos + 1, P - LastPos - 1)
LastPos = P
i = i + 1
P = InStr(LastPos + 1, sLine, ",", vbBinaryCompare)
Loop
A(i) = Mid$(sLine, LastPos + 1)
End Sub
This extra comma is causing another field to be created.
Dim F As Long, sLine As String, A(0 To 4) As String
Dim db As Database, rs As Recordset
F = FreeFile
Open "c:\temp\test.txt" For Input As F
Set db = CurrentDb
On Error Resume Next
On Error GoTo 0
Set rs = db.OpenRecordset("TestImport", dbOpenTable)
Do While Not EOF(F)
Line Input #F, sLine
ParseToArray sLine, A()
rs.AddNew
rs(0) = A(0)
rs(1) = A(1)
rs(2) = A(2)
rs(3) = A(3)
rs(4) = A(4)
rs.Update
Loop
rs.Close
db.Close
Close #F
End Sub
Sub ParseToArray(sLine As String, A() As String)
Dim P As Long, LastPos As Long, i As Long
P = InStr(sLine, ",")
Do While P
A(i) = Mid$(sLine, LastPos + 1, P - LastPos - 1)
LastPos = P
i = i + 1
P = InStr(LastPos + 1, sLine, ",", vbBinaryCompare)
Loop
A(i) = Mid$(sLine, LastPos + 1)
End Sub
