WebmasterSite.net: PHP scripts to enable your creativity
WSN Links PHP Directory Software
PHP Scripts Webmaster Links Support Forums

Register | Forgot Password

csv import with comma in field

Version: 4.0.39
printPrint


csv import with comma in field
hai
Forum Regular
Avatar

Usergroup: Customer
Joined: Apr 03, 2007
Total Topics: 45
Total Posts: 187
Posted 09/12/07 - 07:15 PM:
quote post
#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
Avatar

Usergroup: Customer
Joined: Apr 03, 2007
Total Topics: 45
Total Posts: 187
Posted 09/12/07 - 08:15 PM:
quote post
#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
Avatar

Usergroup: Administrator
Joined: Dec 21, 2001
Location: Northern California
Total Topics: 55
Total Posts: 6022
Posted 09/12/07 - 10:49 PM:
quote post
#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
Search thread for
Download thread as


You don't have permission to post.

Please login or register.

   
 
© 2008 Paul Knierim. All rights reserved.