comma in data conflicts with comma field delimiter option with csv import into access

Hi all

Anyone know how to get around this problem

I have a CSV file I import into Access using an import spec. I go with comma as the field delimiter. This works fine for most of the records

However, there is a description field as part of the import. If in the description data, there is a comma, then the import thinks this is the end/beginning of the field and everything shifts over 1 field. i have several records that have this problem

Why would anyone think that using a comma as a field delimiter was a good idea, knowing you could have a comma in the data itself.

Anyway, is there a way around this?

Who is Participating?
HenryV1955Connect With a Mentor Author Commented:

I found this doc

which says
Change the separator in all .csv text files
1.In Microsoft Windows, click the Start button, and then click Control Panel.
2.Open the dialog box for changing Regional and Language settings.
3.Type a new separator in the List separator box.
4.Click OK twice.
Note    After you change the list separator character for your computer, all programs use the new character as a list separator. You can change the character back to the default character by following the same procedure.

I tried this , changed the list separator to a tilde and works fine
Paul MacDonaldConnect With a Mentor Director, Information SystemsCommented:
Can you wrap the data in quotes (single or double, as appropriate)?  That's probably the answer to your question.
etech0Connect With a Mentor Commented:
Where is the csv coming from? Maybe there's a setting to wrap the data in quotes.

If it's not possible, you can use VBA in excel to add the quotes before importing to access.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
As the others have said, you can wrap text fields in quotes or the other option is to use a different delimiter, such as a tab.

Standard csv files created by Access surround text fields with double quotes.

"some field", 22.45, "another text field", "this text field might contain a crlf"

The only problem you will have with this standard is if your text strings might contain a double quote.  If that is the case, it must be "escaped".  That means doubled.

"pipe is 6"" long", 14.555, "another field"
HenryV1955Author Commented:
Hi guys

I gave everyone points for this

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.