Importing csv file using Import and Export Data Wizard

armgon
armgon used Ask the Experts™
on
I have to import a .csv file that contains any item in a field with (") quotes as double quotes.

See sample record below:

100005,"Amerimax Home Products","5"" Aluminum Gutter",

What is the correct et up for the Wizard Screen to handle these fields accordingly?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could import the data without a "Text qualifier". This way the product will be imported as:
"5"" Aluminum Gutter"
(with all the quotes). After the import you can run an update statement to remove the quotes at the start and end, and replace the double quotes with a single quote. But his will cause problems when you have a comma in your text fields. I you can control the export you can choose to use delimiters and text qualifiers that are not present in your data. Otherwise you might be able to import the file in Excel or Access first (not sure if those can handle the quotes correctly) and export it with different delimiters from there.
I am able to solve this problem pragmatically. While doing wizard you have to select double quote " as Text Qualifier .

See the attached file. Remember put double quote only once in Text Qualifier. (do not put "")

Let me know if it helps.
TextQualifier.GIF

Author

Commented:
Unfortunately putting in the quotes as the text qualifier works for the first record but after that fields get thrown out of wack. Mostlikey do another field perhaps having a comma that really needs to be in quotes to be pick up as part of the text field.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

can you share ur csv?
Commented:
Could not find a solution so we rewrote the import on the otherside to accept a tab delimited file which could be produced without creating any confusion around the ".

Author

Commented:
could not wait any futher to product the export file to be used by the other server to import our data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial