Exporting from Excel to a CSV with a delimiter other than a comma. It is possible!

I was surprised today to find that when I had a need to export an Excel file out to a CSV file that there wasn't anywhere within Excel (that I could find at least) to change the delimiter from a comma to something else.

The reason I needed to change the delimiter was that the text had commas in some of the fields, and although the text was put into double quotes by Excel this wasn't suitable for me importing easily via PHP into a MySQL database. I was initially wanting to import the xls file via PHP into MySQL but there was a lack of code available to support this, so I decided to stick with the CSV option.

After a bit of searching I came across a blog post from 2005 that had the answers, and I thought it worthy to replicate it here for when I or others need it:
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 choosing to 'Save As' CSV.
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.
 Big thanks to the "Astrochimp: Export CSV with Any Delimiter" post.

Comments

  1. The free OpenOffice Calc (their spreadsheet program) will let you specify the field delimiter and text delimiter when you save as CSV. When you click "Save As" CSV, tick the "Edit Filter Settings" checkbox. It will then prompt you for the character set, field delimiter and text delimiter.

    ReplyDelete
  2. Hi Simon, in case you planned an update on the subject:
    Changing regional settings may affect MS Access data and changing settings back and forth is exhausting. There is a solid 3rd-party solution https://xltools.net/export-to-csv/ - choose semicolon, or tab or pipe for a delimiter. This is helpful when I have to convert to CSV often.

    ReplyDelete

Post a Comment

Popular posts from this blog

IT & Enterprise Architecture Conference 2015 - Day 2

Considerations when responding to an RFI or RFP (a view from the receiving end)

Using Raspberry Pi as a Time Capsule to backup a Mac