vba importing csv file with quotes

[griffins69]

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

 

[JamesMcS]

Try getting the file separated by another character, or tabs maybe?

 

[griffins69]

not an option

 

[JamesMcS]

Out of ideas then, as far as I know Access will always treat a comma in a Comma Separated Values file as "time to create a new field".

 

[DCrake]

Is there anyway that you could get the source file to change its delimiter to say a | pipe symbol or a Tab?

 

[JamesMcS]

I did ask the OP Mr. C, but I got "not an option" back....

 

[boblarson]

If your text fields are delimited by quotes then you should be able to import it using the DoCmd.TransferText code with an import specification.

Doing it the way you are is much more difficult.

 

[DJkarl]

Are there always double quotes around all text fields in the file, if so you can setup an import spec and use Docmd.Transfertext to import the file. In the import spec you can specify a text delimiter such as "

If you really need to import the file yourself you will need to setup an additional check in your loop. If the character is a " then ignore any commas until after the next ".

- Lol Bob beat me to the punch

 

[DCrake]

You all beat me. The post post was sat on my screen for ages before I decided to respond.

 

[griffins69]

how do I ignore commas until the next double quote?

 

[boblarson]

how do I ignore commas until the next double quote?

Is there a reason why you can't use the TransferText command? Trying to ignore commas until the next double quote is really going to be a lot of extra code as compared to the other function.

 

[griffins69]

It would help if could be it with using the code.

 

[boblarson]

It would help if could be it with using the code.

Sorry, but that is an unintelligble sentence. Can you try again? Why can't you use DoCmd.TransferText along with an import specification?

 

[gemma-the-husky]

instead of using docmd.transfer text, read the data in a line at a time, and process it in code.

you could then do a split to determine if there are problems with any of the rows. before deciding how to handle the problem.

 

[boblarson]

instead of using docmd.transfer text, read the data in a line at a time, and process it in code.

Umm, that's what they are already having trouble with Dave. I suggest trying the Docmd.TransferText to see if that can help because you can state that a given field is delimited by double quotes and therefore it won't have the comma issue that they are having by trying to read it line for line in code.

 

[gemma-the-husky]

i should read posts more carefully

i casually misread, and thought it was the other way round.

------------------
in that case, using transfer text makes sense - that should identify the correct number of columns, shouldnt it.

 

[boblarson]

in that case, using transfer text makes sense - that should identify the correct number of columns, shouldnt it.

I would think it would. It is just a matter of setting up the import spec, if there isn't any strange set up to it.