Access import CSV file with comma issue

[Boon]

Hello,

I work a lot with importing the csv file into the database. The annoying
problem I have is when there is a comma in a csv file, it screw up the
import.

I am not sure whether I shuld do anything in the Access, or doing in the csv
file itself. Basically, I have another program that generates the csv file.
then I import the csv file into my Access database.

Should I focus on the import in Access, or focus on making sure that csv
file does not contain extra comma.

every suggestion is very welcomed.

thanks,
Boon

 

[Douglas J. Steele]

CSV means "comma separated values". The position of the commas is critical!

You'll have to provide more details about the problem.

 

[Marshall Barton]

I work a lot with importing the csv file into the database. The annoying
problem I have is when there is a comma in a csv file, it screw up the
import.

I am not sure whether I shuld do anything in the Access, or doing in the csv
file itself. Basically, I have another program that generates the csv file.
then I import the csv file into my Access database.

Should I focus on the import in Access, or focus on making sure that csv
file does not contain extra comma.

SInce it may be impossible to tell whether a comma is part
of a field or a field separator, I suggest you do something
in the export process. It may be as simple a replacing the
commas in a field with a different character and then
replacing again using a update query after the import.

 

[John W. Vinson]

Hello,

I work a lot with importing the csv file into the database. The annoying
problem I have is when there is a comma in a csv file, it screw up the
import.

I am not sure whether I shuld do anything in the Access, or doing in the csv
file itself. Basically, I have another program that generates the csv file.
then I import the csv file into my Access database.

Should I focus on the import in Access, or focus on making sure that csv
file does not contain extra comma.

every suggestion is very welcomed.

thanks,
Boon

If the text fields in the csv file are delimited with quotemarks, there
*SHOULD* be no problem: e.g.

"ABC","this, string, contains, commas", 254

will be seen as two text strings and a number.

Of course if your numbers contain commas as thousands separators, or the text
strings aren't delimited, you'll have a problem!

Could you post a sample of the data - or are you the same person who posted
this question last week...?

 

[Boon]

Thanks all. Sorry for late reply...

I will try the quote suggestion and see if I can do something.

Here is the situation I have.

I use Adobe Acrobat to collect the survey data from several people (around
100). When Acrobat export the response file into CSV format, the file
displays incorrectly in Excel. (The problem is extra comma). I think what I
will do next is to figure out when exporting from the Acrobat if I can put a
quote for each field (column).

thanks.

 

[John W. Vinson]

Thanks all. Sorry for late reply...

I will try the quote suggestion and see if I can do something.

Here is the situation I have.

I use Adobe Acrobat to collect the survey data from several people (around
100). When Acrobat export the response file into CSV format, the file
displays incorrectly in Excel. (The problem is extra comma). I think what I
will do next is to figure out when exporting from the Acrobat if I can put a
quote for each field (column).

That would certainly be the approach. I'm not sure what relationship the
question has to Microsoft Access at this point, though!