csv import with comma in field: WSN Support

Version: 4.0.39

csv import with comma in field

hai
Forum Regular

Usergroup: Customer
Joined: Apr 03, 2007
Total Topics: 45
Total Posts: 187

Posted 09/12/07 - 07:15 PM:	#1
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

hai
Forum Regular

Usergroup: Customer
Joined: Apr 03, 2007
Total Topics: 45
Total Posts: 187

Posted 09/12/07 - 08:15 PM:	#2
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

Paul
Administrator

Usergroup: Administrator
Joined: Dec 21, 2001
Location: Northern California
Total Topics: 55
Total Posts: 6022

Posted 09/12/07 - 10:49 PM:	#3
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.
"Do things you love doing, because then it ain't work. Don't do something you don't really enjoy, because you're never going to work hard enough at it." - Bob Young, founder of Red Hat

You don't have permission to post.