Import csv with comma's in a field

[RainX]

Hi all,

I have a csv file which seperates each field with a comma.
Now i have some fields that contain comma's within them but they are enclose in quotation marks. How would i import it so that it doesnt seperate these fields?

Thanks in advance

 

[FoFa]

Tell the import your text fields have quotes around them. You may have to use an import specification to do this.

 

[RainX]

How would i tell it to do that?
Sorry im a bit of a newb at this.
Also not all the text fields have quotes around them
Just the ones containing the comma do.
That field is also mixed with no quotes as well..

Thanks again

 

[The_Doc_Man]

No Access function will do this for you correctly. Your problem is that your input data is in a hodge-podge of formats, i.e. mixed quoted and non-quoted fields. The Access wizard will not do it at all.

The way I see it, you have two choices (or maybe three depending on the size of the file to be imported and the frequency of such imports):

1. Force the source of this data set to output in a regular format. For any situation where you have large or frequent data set conversions, this is the best possible solution. NOTE: Regular format could imply fixed punctuation, such as all fields are quote-enclosed, OR it could be fixed width, where a field is always the same size in the putput. Access could handle either case.

2. Edit the data set manually before importing. Not good for large data sets or the case where you will do this import often.

3. Write code to parse the input looking for commas and quotes, breaking fields on commas that are outside of quotes but keeping paired quotes intact regardless of what goes between them. Only do this if you have large or frequent data conversions and cannot force the output format.

You ARE going to do one of these. It is up to you as to which one is best.

 

[RainX]

Okay thanks alot. I'll look into the 3rd option cause the file contains over 1 million lines.

Take care

 

[FoFa]

Sounds like a std CSV file. When you do the import, use the ADVANCED button and you can tell it the text identifier is a quote, then it will ignore commas within those quotes, and remove the quotes.

 

[JohnC1939]

Late on this one, only just joined!
If you create a temprary table of the records to be exported, a function such as that below can be used to modify the temporary records before export. Note that you need only open the recordset as "rsTemp" and loop through it to make all the changes, calling the function on each record. The table can then be exported using a simple Print# statement, or by using the Access command.

Public Function funcChangeCommas()
Dim strCommaText As String
Dim varCount As Variant
For i = 0 To rsTemp.Fields.Count - 1
If InStr(rsTemp.Fields(i), ",") > 0 Then
strCommaText = rsTemp.Fields(i)
Do While InStr(strCommaText, ",") > 0
varCount = InStr(strCommaText, ",")
strCommaText = Left(strCommaText, varCount - 1) & ":" & Mid(strCommaText, varCount + 1)
Loop
rsTemp.Edit
rsTemp.Fields(i) = strCommaText
rsTemp.Update
End If
Next i
End Function

HTH