Solved

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

Posted on 2014-10-21
7
317 Views
Last Modified: 2014-11-13
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.
0
Comment
Question by:pcalabria
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 38

Accepted Solution

by:
PatHartman earned 167 total points
ID: 40395813
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
 
LVL 25

Expert Comment

by:NVIT
ID: 40395825
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
 

Author Comment

by:pcalabria
ID: 40396065
Pat.. Yes. double quotes used as delimiter.

NewVillage..sorry I didn't understand can you explain?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 25

Assisted Solution

by:NVIT
NVIT earned 167 total points
ID: 40396072
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
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 166 total points
ID: 40396284
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
 

Author Closing Comment

by:pcalabria
ID: 40438796
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40439411
You are welcome!

/gustav
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question