Export CSV with Any Delimiter
When working with databases you often find yourself in the frustrating situation of loading a database from an Excel file. The usual solution is to save the Excel file as a .csv file from which you can split out the fields. Problems occur when your fields contain commas. Exporting as a tab separated list does not solve this issue since blank fields do not get parsed consistently. The solution would be to use a different character as the delimiter, but Excel does not provide an obvious way to export data with a delimiter that is not a tab or comma. There is a little known setting in Windows can solve you problems. You can set any character to use as your delimiter when saving an Excel file as a csv. The key is in Windows Regional Settings:
To change the default on your PC to a pipe rather than a comma bring up your default options window by clicking Start -> Settings -> Control Panel -> Regional Settings.
Click the “Number” tab and in the “List Separator” field, replace the current default separator with the one you want to use (let’s say a pipe symbol | ).
Click “OK” to save the change and close the window. You can now save Excel files as pipe delimited files by simply doing the following in Excel:
This is a huge time saver since I either would have to write out a regular expression to parse out the escaped commas or try to use Coldfusion to query the Excel file.
** Update ** : In Windows XP there is an extra step involved.
When you open the Regional settings window, you have to click customize next to the language choice drop down. That will bring up another window with “Numbers” as the first tab item. Change the “List separator” to whatever you want to use as a delimiter. Thanks to Adam for the update.
Filed under Uncategorized.
.
Subscribe to this feed.
on February 3rd, 2006 at 11:35 am
Thank you Thank you for this great tip!!! It has made my job so much easier for data conversion.
on March 29th, 2006 at 6:28 pm
Excellent solution.
Microsoft always thinks it knows best and that you need to things their way…
Up yours M$
on June 6th, 2006 at 11:57 am
I was searching for this in too many websites. You made my life easier.Thanks a lot !
on June 14th, 2006 at 7:26 pm
I manually did a change from commas to pipes | in Word or something last time, but this is fantastic, and such a time-saver.
Thanks heaps!
on May 11th, 2007 at 12:49 am
This is the solution I was looking for. Thanks a lot !
on August 2nd, 2007 at 2:30 pm
Great tip!
Just a note on XP, after Regional and Language Options, you have to click on Customize on the Regional Options tab to access List Separator.
on September 3rd, 2007 at 3:06 am
I really appreciate your observation. I’ve been using XP for years now but didn’t care to explore on “Regional & Language Options” .
Great Tip , You saved my Friday Night ;) else id be working all night long trying to figure out whats happening.
on September 4th, 2007 at 6:25 pm
Fantastic tip. Saved me hours. Thanks for sharing.
It’s a bit different on Vista though.
Start -> Control Panel -> Regional and Language Options -> Formats (Tab) -> Customize this format (Button) -> Numbers(Tab) -> List Separator (Field)- Change that to comma or pipe or hyphen or whatever it allows.
on September 26th, 2007 at 2:17 pm
Thank you!!! We needed the pipes delimiter to get a major project working!
on October 9th, 2007 at 8:17 pm
Great tip!! Saved a lot of time from breaking our heads!!
on June 20th, 2008 at 8:27 am
Hi,
excellent tip, thank you very much
on June 23rd, 2008 at 7:13 pm
Fantastic Tip !!!!!!!!
Saved me zillions of hours !!
on July 18th, 2008 at 4:37 am
this is very helpful!..
tnx for sharing!…^^
on July 29th, 2008 at 1:13 pm
A time tested solution, but I have definitely run into occasions when my data contained single pipe characters.
A developer I met liked to use a series of 10 pipe characters, and he was able to easily generate these from non-Windows systems. These files never failed to import into SQL 2000 or 2005.
In Windows XP, the list separator in Regional Settings permits 3 consecutive characters for a delimiter.
on September 4th, 2008 at 7:27 pm
what can i say.
thanks to you for putting this up.
thanks to google that this shows up bang on the top. first link.
on September 16th, 2008 at 10:46 am
That was a very useful tip. I have XP and I changed the comma to pipe.Thanks for sharing.