How to export an Excel file to pipe delimited file rather than comma delimited file

Barry Stevens
July 16, 2015
Barry Stevens

Recently 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 Stevens is part of our Data Ops team who specialises in SAS Administration, helping you prevent chaos.

Connect with Barry on LinkedIn or read some of his other blogs here.

Copyright © 2019 OptimalBI LTD.