Select Page

Barry StevensRecently I was tasked to create a pipe delimited file from an Excel Spreadsheet, a simple task you would expect.  On first glance it was not as simple I thought.

I use Excel 2010 and was expecting to be able to do something like ‘save as’ and select the file type ‘CSV (Comma delimited)(*.csv)’ and then have the option to select the delimiter like when importing a csv file.  After a little head scratching and a quick google, I found I had to make a global setting change in the control panel (Yes, that’s right).

So in Windows 7, I found you must do the following to change the delimiter:

  1. Make sure Excel is closed
  2. Navigate to control panel
  3. Select ‘Region and Language’
  4. Click the ‘Additional Settings’ button
  5. Find the List separator and change it from a comma to your preferred delimiter such as a pipe (|).
  6. Click OK
  7. Click OK
  8. Exit Control panel
  9. Open the Excel file you want to export to a pipe delimited file
  10. Select File, Save As
  11. Change the ‘Save as type’ to ‘CSV (Comma delimited)(*.csv)’
  12. Change the name and file extension if you want, by default stays as csv even though a different delimiter
  13. Click Save
  14. Click OK
  15. Click Yes

And that is it, easy right? Hmmmm – if you only wanted to change the delimiter once not an easy/simple process.

Ok, it is not as bad as I make out. For example, if you want to have tab delimiter or fix width (space) there are save as file type options available, however, given how easy the import wizard is I would have thought they would have done something similar.

Have I missed something or has google put me wrong? If so let me know…

Barry, Preventer Of Chaos.

Barry blogs about how to stop chaos in your systems

Read Excel – Using Text Filters to find out how to filter based on the structure of information or Barry’s blogs here.

Visit our Teach page to find out how our courses can help learn how to get the best from your data.

%d bloggers like this: