I'm sorry to bother you again with another import problem, but I'm hoping you can figure out a way around this one as well.
I am trying to import a csv file, unfortunately I realized tonight that some of the fields already include a comma. For example, I have a field with the following information - Clapton, Eric and another field with London, England. When I export from excel it lists them as "Clapton, Eric","London, England" to make up for the additional commas. When I tried importing it imported
first field - "Clapton second field - Eric" third field - "London etc.
I can try to change it to a tab-delimited file, but there is no way to tell wsn that the separator is now a tab and not a comma. I also do not need to have the quotes imported in most cases, but excel puts double quotes around any information that actually has quotes in the data itself. So for example, I do not need the quotes imported for "Clapton, Eric", but I would want them imported for "Crossroads" - which would export as ""Crossroads"".
Did a little bit of searching online and found a way to have excel use a pipe delimiter "|". When I exported this way most of the quotes were gone except in the places where they were doubled. A quick "search & replace" from "" to " within notepad did the trick and everything imported correctly.
For anyone who is interested here is how you can change from exporting using a comma as a delimiter to a pipe (or anything else).
Click Start -> Settings -> Control Panel -> Regional Settings - in Windows XP click customize next to the language choice drop down.
Click the "Number" tab and in the "List Separator" field, replace the current default separator with the one you want to use (ex. | ).
Click "OK" to save the change and close the window. Close and restart excel if you already had it open. You can now save Excel files as pipe delimited files by simply doing a "File > Save As".
TSV -- tab separated values -- is a more standard and easier way of avoiding commas-inside-data issues. Any decent spreadsheet program (don't know about excel) will give you the option to change the separator when saving a CSV. In KSpread, for example, on saving a CSV there's a "Cells" tab where you can change the separator from comma to tabulator.
For a TSV you can specify \t as the separator in the flat file importer.
0/5
1
2
3
4
5
This thread is closed, so you cannot post a reply.
Comments on csv import with comma in field
Forum Regular
Usergroup: Customer
Joined: Apr 03, 2007
Location: NY & PA
Total Topics: 94
Total Comments: 339
Dear Paul,
I'm sorry to bother you again with another import problem, but I'm hoping you can figure out a way around this one as well.
I am trying to import a csv file, unfortunately I realized tonight that some of the fields already include a comma. For example, I have a field with the following information - Clapton, Eric and another field with London, England. When I export from excel it lists them as "Clapton, Eric","London, England" to make up for the additional commas. When I tried importing it imported
first field - "Clapton
second field - Eric"
third field - "London
etc.
I can try to change it to a tab-delimited file, but there is no way to tell wsn that the separator is now a tab and not a comma. I also do not need to have the quotes imported in most cases, but excel puts double quotes around any information that actually has quotes in the data itself. So for example, I do not need the quotes imported for "Clapton, Eric", but I would want them imported for "Crossroads" - which would export as ""Crossroads"".
Thanks in advance for your help.
Marilyn
Forum Regular
Usergroup: Customer
Joined: Apr 03, 2007
Location: NY & PA
Total Topics: 94
Total Comments: 339
Did a little bit of searching online and found a way to have excel use a pipe delimiter "|". When I exported this way most of the quotes were gone except in the places where they were doubled. A quick "search & replace" from "" to " within notepad did the trick and everything imported correctly.
For anyone who is interested here is how you can change from exporting using a comma as a delimiter to a pipe (or anything else).
Click Start -> Settings -> Control Panel -> Regional Settings - in Windows XP click customize next to the language choice drop down.
Click the "Number" tab and in the "List Separator" field, replace the current default separator with the one you want to use (ex. | ).
Click "OK" to save the change and close the window. Close and restart excel if you already had it open. You can now save Excel files as pipe delimited files by simply doing a "File > Save As".
Marilyn
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
TSV -- tab separated values -- is a more standard and easier way of avoiding commas-inside-data issues. Any decent spreadsheet program (don't know about excel) will give you the option to change the separator when saving a CSV. In KSpread, for example, on saving a CSV there's a "Cells" tab where you can change the separator from comma to tabulator.
For a TSV you can specify \t as the separator in the flat file importer.