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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul MacDonaldDirector, Information SystemsCommented:
Can you wrap the data in quotes (single or double, as appropriate)?  That's probably the answer to your question.
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HenryV1955Author 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.