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

By -
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. Greg BakkerMarch 16, 2012 at 7:57 AM

    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. eriklindMay 14, 2016 at 2:10 AM

    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

Solution: Getting macOS Messages to work with Google Chat / Talk / Hangouts in High Sierra

By -
Image
The Messages application wasn't working for me on macOS and I was wanting it to work with my Google Chat / Talk / Hangouts service. It appears that with macOS High Sierra a workaround is required. After struggling to get it going I finally got it going after some searching on the Internet and trying a couple of supposed fixes. What ended up working for me were the instructions on  https://forums.macrumors.com/threads/gtalk-messages-solution-found-high-sierra.2073393/ with a couple of tweaks. The tweaks were required to support 2FA which is enabled on my account. The instructions, repeated here so that I can find them again if required and to assist others having the same problem, are as follows: Go to System Preferences / Internet Accounts and delete your google account. Open Terminal and run the following commands (Warning: This will delete your history!): find ~/Library/Preferences -name "*iChat*" -delete rm -rf ~/Library/Caches/com.apple.Messages rm -rf...
Read more

Using Raspberry Pi as a Time Capsule to backup a Mac

By -
Image
Time Machine is the built-in backup feature of OS X. It keeps a copy of all your files, and remembers how your system looked on any given day so you can revisit your Mac as it appeared in the past. Apple sells a device called a Time Capsule, but rather than buy one of these I opted for a diy approach using my  Raspberry Pi  and so far it seems to be working well. My setup consists of: Raspberry Pi Model B running Raspbian connected via network cable to a router MacBook Pro running OS X Yosemite v10.10.1 connected via Wifi to a router External 2TB drive (with separate power) attached via USB to the Raspberry Pi It was relatively easy to setup following the instructions at  http://www.raspberrypi.org/forums/viewtopic.php?f=36&t=47029  . I am reiterating the instructions here just in case the page disappears (with a couple of additions I needed): 1. Start with a clean installation of Raspbian, configured for your network 2. Power down your Pi, con...
Read more

IT & Enterprise Architecture Conference 2015 - Day 2

By -
Today was the second and final day of the IT & Enterprise Architecture Conference 2015. Below is a summary of my key notes. Going beyond IT. What EA can really mean for your organisation John Pearson, Business Architect, IAG Consider the Demand vs Supply side of architecture IAG is using traditional TOGAF domains (Information, Application, Technology Infrastructure and Security) with business architecture being used to better align with the demand from the business Enterprise Business Motivation Model (EBMM) - Accenture, Nick Malik ( http://www.motivationmodel.com/ ) anchor diagram for business architecture using to understand change impact Bake business architecture approaches in early in the architecture journey Understand the business, its priorities and where value can be added Understand architecture capabilities in service to the business Understand key stakeholder n...
Read more