I need help importing a CSV file in MS Access 2000 that has commas embedded.

Hello Experts,

I am using Access 2K on Windows XP Pro.

I am trying to import a text file that includes commas.
I know that the data should be enclosed in double quotes but this is not working for me.

Here is the text:
Line Number,Action,Status,ErrorCode,ErrorMessage,Code,Message,ItemID,ReferenceID,ApplicationData,StartTime,EndTime,AuctionLengthFee,BoldFee,BorderFee,BuyItNowFee,CategoryFeaturedFee,CurrencyID,FeaturedFee,FeaturedGalleryFee,FixedPriceDurationFee,GalleryFee,GiftIconFee,HighlightFee,InsertionFee,InternationalInsertionFee,ListingDesignerFee,ListingFee,PhotoDisplayFee,PhotoFee,ProPackBundleFee,ReserveFee,SchedulingFee,SubtitleFee,CustomLabel,PrivateNotes,BasicUpgradePackBundleFee,ValuePackBundleFee,ProPackPlusBundleFee,SellerInventoryID,CrossBorderTradeNorthAmericaFee,CrossBorderTradeGBFee,RefundFromSeller,TotalRefundToBuyer,CorrelationID
2,Add,Warning,"21916981-20169","Warning - Ignore Shipping Cost when free shipping is enabled.--Warning - Invalid store category ID, 0, since it is non-leaf.  So item has been listed to the Other store category-0, since it is non-leaf.  So item has been listed to the Other store category-",,,281474543711,,,2014-10-21T20:41:22.526Z,2014-11-20T20:41:22.526Z,0.0,0.0,0.0,0.0,0.0,USD,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0000-000784,,0.0,0.0,0.0,,,,,,,

The problem begins when it reads this field:

,"Warning - Ignore Shipping Cost when free shipping is enabled.--Warning - Invalid store category ID, 0, since it is non-leaf.  So item has been listed to the Other store category-0, since it is non-leaf.  So item has been listed to the Other store category-",

As soon as it hits the comma after ID, the import gets messed up.

Here is my code to do the import:

'Import the first file in the Import Directory into the TEMP file
    DoCmd.TransferText acImportDelim, "File Exchange Response Import Specification", "FileExchangeResponseTEMP", strFilePath, True

MS Excel 2000 successfully imports the file.
pcalabriaAsked:
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.

PatHartmanCommented:
Do you also have double quotes selected as the text delimiter in your import spec?  Technically the .csv format is very flexible and only the actual fields that need the extra text delimiting because they contain the field delimiter need to be enclosed in quotes.
0

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
NVITCommented:
Have you tried other Import specs? e.g. Try changing the Import specification manually via File, Get External Data, Import, Files of type (try Text Files)
0
pcalabriaAuthor Commented:
Pat.. Yes. double quotes used as delimiter.

NewVillage..sorry I didn't understand can you explain?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

NVITCommented:
If you have Access 2000 program available, you can see if it works with a manual import. Be sure to do it using a test table file, not your production table.

From the menu: File, Get External Data, Import, Files of type (Text Files)
Select your csv file.
Pick Import.
In the Wizard, pick Advanced.
Delimited
Field delimiter: ,
Text qualifier: "
Set other options as desired.
Save as: PertinentFilename
OK.
Follow through the wizard to see if it works.

If it works, try your code with the new spec:
DoCmd.TransferText acImportDelim, "PertinentFilename", "FileExchangeResponseTEMP", strFilePath, True
0
Gustav BrockCIOCommented:
All you need is to carefully adjust an import specification - click Advanced and proceed.

It is saved in the attached database as CustomImportSpecification.
Right-click the file, Save As .., change the file name to imp.mdb.

/gustav
imp.mdb
imp.txt
0
pcalabriaAuthor Commented:
Thank you all!

My problem is solved, and all of your made suggestions that helped me find the solution.  The first problem was that I did not have the text qualifier set to ".  I believe this is what Pat was saying, but the term delimiter through me off track as I thought she was referring to a text delimited file.  My oversight.

Second, I was not able to change the import specification because of a problem with Access, and did not have the original disc available to run the repair option.

All is importing properly now, with the text qualifier properly set, and I've been able to repair my installation so I can even create new export specifications.  

Thanks again!
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
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.