Access import CSV file with comma issue


B

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
 
Ad

Advertisements

D

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.
 
M

Marshall Barton

Boon said:
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.
 
J

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...?
 
B

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.
 
Ad

Advertisements

J

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!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top