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

by | Jul 16, 2015

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.

53 Comments
  1. Martin Burley

    If you’re doing this kind of thing often, you can write a macro to automate it. The macro needs to: concatenate the required columns, adding pipes between them; copy-paste the data to a new workbook and format the cells as text; save the new workbook as a text file. Your basic point stands, however: it shouldn’t be this tricky!

    Reply
  2. Barry

    Hi Martin.
    Thanks for taking the time to read and reply to my blog. Yes a macro to automate makes sense if needed on a regular basis, fortunately for me this was a once off.
    I am still surprised that it wasn’t as simple as other delimiter types, the question I suppose is why?
    Barry

    Reply
  3. Barry Stevens

    Hi Aryeh,
    Thanks for reading my post.
    Unfortunately I haven’t got a macro that does this as I only need to do this very occasionally so do manually. I do see that a macro would be very beneficial if doing on a regular basis, maybe hopefully reader has done this before and can share.
    Regards Barry

    Reply
  4. shelby austin

    yp didnt work for me.. i already had a csv. i updated the settings reopened the CSV in notepad and it still had all the commas. do i have to start with an unsaved excel file for this to work?

    Reply
  5. Barry Stevens

    Hi Shelby,
    The intention of this blog was exporting to a pipe delimited file from an Excel (xls) file not a comma delemited (.csv) file, so try saving the csv as an xls file and then follow the instructions. This may have it’s limitations if your CSV file contains more records/rows that your version of Excel can handle.
    There may be better/easier/quicker ways to do what you want, such as a simple change replace of “,” to “|” via a text editor if your data doesn’t include commas.
    Regards
    Barry

    Reply
  6. KritiSundar

    I usually use Textpad tool for this. I copy paste the content of excel into textpad editor and press F8 and then in find give \t and in replace give | (pipe) . check the Regular expression option and do replace all. Thats it. Problem solved.

    Reply
  7. Barry Stevens

    KritiSundar,
    Thanks for reading and commenting on this post. Like you have suggested there are other ways to do this; And a very simple solution too. The intention of the post was to demonstrate how to do with it Excel and no other tool while also implying a question on why is it so tedious for this example.
    Once again thanks for your suggested solution.
    Regards Barry

    Reply
  8. Denny

    I am exporting an excel file to csv. I changed the List Separator from a comma (,) to a pipe (|). The problem is there are commas used in text in the cells and it has changed them to (|) as well. How the heck do you get it to leave the text in the fields alone and only separate the columns???

    Reply
  9. Barry Stevens

    Hi Denny. If you follow my steps above and export an excel file (with the excel file extension not csv opened in excel) it should handle what you want (it is exactly what I needed to do and I had ‘,’ in the data too). Barry

    Reply
  10. Jeanne Irwin

    Hi – I’m following everything and this maybe a dumb question but how do I create the actual “pipe” character. I don’t see it on my keyboard. Is it just a capital i? It looks bigger. thanks Jeanne

    Reply
  11. Erik

    Hi Barry,
    It doesn’t work for me. 🙁
    I did exactly what you explained, even rebooted my computer to hope the changes would have any effect, but even after a reboot, the saved CSV will still have ; instead of , ….
    The Region and Language settings are correct, so the list separator is configured with a , not a ; .
    Even when I open up a new Excel window and a Blank Workbook, fill in some data and save as CSV (Comma delimited) it still is being saved with ; .
    So….. i am very sad because things aren’t working the way I would like to…..
    Best regards,
    Erik.

    Reply
  12. Barry Stevens

    Hi Erik
    Thanks for reading my post, sorry to hear that it hasn’t helped in your case. The intention of the post was to demonstrate how to do with it Excel (with office 2010 and windows 7) and no other tool while also implying a question on why is it so tedious for this example.
    Unfortunately not knowing your environment I am unable to help.
    Regards
    Barry

    Reply
    • Erik

      Hi Barry,
      I have the same setup as you do, Office 2010 and Windows 7.
      Maybe I haven’t understood well what the purpose is, but in my case what I need is create an Excel file, and save it as CSV, so that when I edit the CSV with e.g. Notepad, the CSV will have comma’s instead of semicolons.
      But as I mentioned before, that didn’t happen by editing the Region and Language settings. 🙁
      Any other options?
      Best regards, Erik.

      Reply
  13. Barry Stevens

    Hi Erik
    Not sure I can be much more help.
    Regards
    Barry

    Reply
  14. Keith

    Yes, very frustrating to perform a simple task. I hate MS office applications in general. They give me high blood pressure.
    The easiest way I can think of to save as a pipe delimited file, is to save the CSV export, then import to MS Access, then MS Access has an option to export the table as a tab delimited file. hopefully that will help some people.
    Take care not to corrupt any data with datatypes, easiest to set everything as text when you import to MS Access.

    Reply
  15. Barry Stevens

    Hi Keith,
    Thanks for taking the time to read my post and suggest an alternative to this frustrating simple task
    Cheers
    Barry

    Reply
  16. Raman

    Hi Barry,
    This post really very helpful and I got the result as stated above. But while importing data from excel to my csv file non latin characters are unable to get copied i.e wherever my non-latin characters are in excel, in csv it shows up as ‘?’. my desired characters are non ASCII (ネンチヤ). Can you please help ?

    Reply
  17. Barry Stevens

    Hi Raman,
    Thanks for taking the time to read my post and I am Glad you have found helpful. Unfortunately I haven’t had any experience with non-latin characters in this scenario so I am not much help on that.
    Regards
    Barry

    Reply
  18. Shaun McGirr

    Hi Raman, Barry’s colleague Shaun here.
    Welcome to the wonderful world of “text encoding”! In general, Microsoft favours character sets that only include Latin characters, and I have faced the same problems working with Russian characters that you face here with Japanese. Unfortunately successive Office versions have hidden the solution further and further from view!
    On Windows:
    – When you have your Excel file ready, save it in Excel format somewhere safe, so you can return to it if needed.
    – Then, go File -> Save As, and choose a location for your CSV.
    – In the “Save as type” box under the file name, select “CSV (Comma delimited) (*.csv)”, then click the “Tools” button next to “Save”, and select “Web Options”.
    – Choose the “Encoding” tab and change the option under “Save this document as” to either “Unicode (UTF-8)” or one of the Japanese options. You might need to experiment a little!
    On Mac: the options above don’t exist, but see my next comment on this post!
    Good luck,
    Shaun

    Reply
  19. Shaun McGirr

    If you try my solution to Raman’s problem on Mac, you’ll find there is no (longer) support for saving CSVs in other encodings.
    For the other problems described above, including Barry’s original use case, the solution I use is LibreOffice or OpenOffice.
    – Install LibreOffice or OpenOffice (both are free and open source)
    – Open your Excel or CSV file, make sure it looks right
    – “Save as”, choose CSV, and check the “Edit filter settings” box
    – Choose encoding (character set), field delimiter (comma, pipe, or whatever else you want), and text delimiter!
    Nobody likes an answer that involves installing new software. But I think the outpouring of CSV-related issues in other folks’ comments support my point above that we would really like these features but they have been taken away (or hidden) in successive redesigns of Office.
    Fortunately, if you do a lot of work with other languages or exotic file formats, LibreOffice/OpenOffice can help.

    Reply
  20. Tonda

    This didnt work, but not sure if I did it right. I took a notepad file and saved in Exel, removed the pipes to clean up and do some math. Now I want to add the pipes back in and get back into note pad. Even if I have to copy paste back into notepad.

    Reply
  21. Baljeet Degun

    Hi Barry, step 5 isn’t available on Mac – how else can a pipe-delimited “CSV” (or “PSV”) file be generated on Mac?

    Reply
  22. Franck Mercier

    Hi all…
    Copy this code sample in a new macro, Declare variables if needed and try this…
    Sub Export_TXT()
    Set a = CreateObject(“Scripting.FileSystemObject”).CreateTextFile(“Export.txt”, True) ‘saved in default user directory
    For r = 1 To Worksheets(1).UsedRange.Rows.Count ‘default index sheet set to 1, change if needed
    enregistrement = “”
    For c = 1 To Worksheets(1).UsedRange.Columns.Count – 1
    enregistrement = enregistrement & Worksheets(1).Cells(r, c) & “|”
    Next col
    enregistrement = enregistrement & Worksheets(1).Cells(lig, col)
    a.WriteLine (enregistrement)
    Next lig
    a.Close
    End Sub
    variables named like this because I’m french, nobody’s perfect !
    Franck

    Reply
    • Piphat P

      May I revise this good macro to get it work smooth as silk?

      Sub Export_TXT()
      ‘Export txt using pipe as seperator
      Set a = CreateObject(“Scripting.FileSystemObject”).CreateTextFile(“Export.txt”, True)
      ‘saved in default user directory
      For r = 1 To Worksheets(1).UsedRange.Rows.Count
      ‘default index sheet set to 1, change if needed
      List = “”
      For c = 1 To Worksheets(1).UsedRange.Columns.Count – 1
      List = List & Worksheets(1).Cells(r, c) & “|”
      Next c
      List = List & Worksheets(1).Cells(r, c)
      a.WriteLine (List)
      Next r
      a.Close
      End Sub

      Reply
  23. Barry Stevens

    Hi Franck
    Thanks for taking the time to read and reply to my post. I am sure others will find your macro useful, thanks for posting.
    Regards
    Barry

    Reply
  24. SF

    This does not work. Stupid Excel is driving me mad, I’m trying to export a file with the fields separated by a comma. Silly me, I assumed CSV would do that. It doesn’t, it separates fields with a carriage return. How do I actually make Excel export CSV with fields delineated with a comma???

    Reply
  25. Miguel Bermudez

    Exactly what I needed. This solution worked great for me.

    Reply
  26. Kate

    worked for me, thanks so much for this post!!!

    Reply
  27. Vijay

    Thanks Barry.
    It worked for me.

    Reply
  28. colleen

    Worked for me. Thanks for your help.

    Reply
  29. Rebecca Winters

    I am following the steps as directed, but when I change the comma to a pipe in the Regional Settings screen, it changes back to a comma when I click Apply. Same when I click OK. How do I make the pipe ‘stick?!?!?’

    Reply
  30. Terry Cruikshank

    If there are not too many columns concatenating the columns also works with “|” as separators.

    Reply
  31. Dwayne Driskill

    A work-around would be to use Excel to save the file as a .csv and then open the .csv file in a text editor and replace the commas with pipes. If the file is large, this will be slow so it would not be a good idea on a large file.

    Reply
  32. Barry Stevens

    Dwayne. That would work unless you have comma’s in your data…

    Reply
  33. Jennifer Cannon

    I do this on a daily basis and simply change the “,” to a “|” in Control Panel/Region and Language/Additional Settings/List Separator. I then save my excel file to a .csv file. It works. (Then I change my “|” back to the “,” because otherwise every time I type a “,”, it would put a “|”)

    Reply
  34. mj

    where is lig, col defined?

    Reply
  35. Ahmed Hafez

    Thanks for sharing this. i was also stuck trying to find such a solution, and you saved me a lot of time. Heart felt thanks
    br
    Ahmed

    Reply
  36. Si

    I used to be an analyst with Excel 97 before moving into web. I’m BLOWN AWAY by the limitations of Excel 2010. Like WOW. Thanks for the article and comments – time to write a macro *sigh*

    Reply
  37. Jivan

    It works like a charm !! thank you

    Reply
  38. Loretta

    here’s how I do it … save the excel file as CSV. Close excel. Right click on the CSV file and open with Word Pad (NOT notepad). Find and replace all commas with pipe. Save as text document-MS-DOS. Works like a charm

    Reply
  39. Barry Stevens

    Loretta That works sometime, unless you actually have ‘,’ (comma’s) as part of the data, you then end up with a | where a comma should be… Thanks Barry

    Reply
  40. Scott

    I have been similarly frustrated by Excel for years. I found myself on this blog via a search to see if there was a way to change the delimiter when saving as a CSV. MS Access has much better exporting options and, as a result, I end up going there to create text files such as this :-S I’ll probably end up there again…

    Reply
  41. John Lichtenstein

    Really good thinking not to make csv files because commas happen. In Python, load the worksheet into a pandas dataframe with read_excel or read_clipboard. Then use a to_csv method with a sep=”|” option on the dataframe.

    Reply
  42. KIrstie

    Life Saver!!! Thank you!

    Reply
  43. ravi

    Thanks a lot for quick solution

    Reply
  44. circusdefect

    I have a quick solution if you are not working with a ton of data. for example if all of your data is column A and B use column C to build your own text string. C1 =A1&”|”&B1. C2 =C1&”|”&A2&”|”&B2. Then just drag this formula down. Copy and paste the value into your new file.

    Reply
  45. Jomy Thomas

    Good Information!
    Note: If you have changed the list separator from , to | then on manually saving an excel file to CSV will generate a pipe delimited CSV.
    However, if you have a code(macro) which is saving an excel to csv format then in the SAVEAS method you need to add a parameter “Local” and set its value to True. If you do not set this parameter, the default value for this parameter will remain false and your code will not refer to the Windows List separator changes at all.
    Hoping this information helps. Thanks

    Reply
  46. Barry Stevens

    Hi circusdefect and Jomy Thomas, thanks for taking the time to read my post, and thanks for the additional tips… Regards Barry

    Reply
  47. Ankit

    This method is not working for me, Closed excel and followed the steps. Still i can see the csv file is comma seprated where in list Seperator is Pipe

    Reply
  48. Vidhya Sivaraju

    Instead of this – “Change the ‘Save as type’ to ‘CSV (Comma delimited)(*.csv)’”

    try to convert to ‘CSV(MS-DOS)(*.csv)..

    Reply
  49. Mario

    Thanks for the solution

    Reply

Trackbacks/Pingbacks

  1. [Solved] Converting A Tab or Comma Delimited CSV File To Pipe Delimited - […] you have to go through the cludgy process of updating the standard delimiter from comma to pipe within MS Office,…
Leave a Reply to Tonda Cancel reply

Your email address will not be published. Required fields are marked *