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.
on November 23rd, 2008 at 7:05 am
great job !!!!! ^_^
on December 13th, 2008 at 5:12 pm
Thank you for the tip!
on March 1st, 2009 at 9:41 pm
Let me know if you have a ‘buy me a beer’ fund I can contribute to.
on March 2nd, 2009 at 2:57 am
Great tips!!i was wondering how come the csv files with the semicolon separator was all jumbled up…now i know why…
on March 25th, 2009 at 9:05 am
This is helpful tip. Thank so much !
on April 3rd, 2009 at 12:32 pm
Many thanks, great tip.
One caveat (XP, german): I needed a comma as delimiter so I changed the “List separator” to a comma. This didn’t work. I noticed that “Decimal separator” (first separator item in the tab) also used a comma. I changed that to something else and there it worked: CSV with *comma* separated values!
on April 16th, 2009 at 3:21 am
As an adjunct: Open Office Calc allows you to choose the delimiters when saving/opening. Just copy and paste the sheet from Excel. Get a portable version of OO from portableapps.com. Couldn’t be easier. you don’t even need to install it.
on May 7th, 2009 at 12:35 pm
Great tip man, saved me a lot of time!
on June 19th, 2009 at 4:48 am
Thx- you saved me some time!
on June 24th, 2009 at 2:12 am
[...] delimited CSV right? Wrong. It is next to impossible to do this in Excel unless you change some system wide settings. To which my first response was [...]
on July 23rd, 2009 at 6:24 am
Thanks for this solutions… I’m looking to fix this since quite few days…
This saved lot of my time..
Thanks
Synergy Informatics
on August 10th, 2009 at 8:53 am
You, my friend, are a hero, a legend, a great person.
on September 17th, 2009 at 12:57 pm
Great solution! Amazing time-saver.
on October 15th, 2009 at 2:07 pm
Thanks — you saved me an hour of so of python programming time :-)
on November 24th, 2009 at 4:12 pm
Thanks a million!!! Like everyone up here, u saved me a lot of time… great tip!
on January 3rd, 2010 at 10:31 pm
Great tip Kung Fu !! Thanks to Google too for showing this right at the top of my search results.
on January 20th, 2010 at 11:48 pm
thank you! i’ve been having trouble with this very issue and came across this on google. what a great idea! cheers!
on February 1st, 2010 at 4:14 pm
thanks just what i need!
on February 11th, 2010 at 6:24 am
Thanks, this has been a gr8 help..
on March 26th, 2010 at 3:19 pm
I created a macro to open an Excel file, refresh the data, then save as pipe delimeted. The saved file is comma delimeted, not pipe. Yet when I manually execute the commands the saved file is pipe delimeted. Do I also need to change a separater default within Excel?
on April 1st, 2010 at 10:03 am
Great tip; thanks very much. Wish I had known about this one years ago!
on April 18th, 2010 at 10:19 am
Hey,
Thanks for publishing this great tip!! Saved me a lot of time.
on April 20th, 2010 at 9:27 am
Unbelievable!!! You saved me *HOURS* of frustration. Excel is such a crock of poo … but I’m forced by my clients to use it. Changing a *system* default to get a simple export option to work is sort of typical for Microsoft.
Many thanks for sharing this little gem :-)
on April 26th, 2010 at 10:01 pm
Thank you. Your tip was exactly what I needed and my problem is solved.
on July 12th, 2010 at 1:57 pm
I discovered this tip through a vendor last year, it works nicely with Excel. But want you to know what I just experience last week. Changing from comma to pipe int he regional settings screws the pooch on Access expressions.
You will get a variety of error and weirdness. Switching back to comma in the regional settings corrected the Access issues.
So now, I will have to switch regional settings to pipe when I need for the Excel pipe csv exports I do and back to comma when I need to use Access.
Thought I would share, because this was very frustrating until i figured out the issue.
Bad dev by MS, to allow virtually unlimited import delimiter options, but nothing on export.
~ Matt