22

Is there a way to tell Excel which delimiter to use when producing a CSV, e.g. tab or pipe?

0
25

Assuming we're dealing with Windows as your OS...

  • Edit your Regional Settings or Windows (Regions and Language).
  • On the Formats tab hit Additional Settings....
  • Change the List Separator to the character you want to use as your CSV separator.

enter image description here

  • Apply those changes.
  • Go back to Excel and save the file as a CSV like you normally would and it should be saved using your chosen separator.

enter image description here

More info/image source

6
  • 1
    For tab separated files, you can just use the built in file format Text (Tab delimited) (*.txt).
    – Richard
    Jul 14 '14 at 18:01
  • 2
    This is not a good solution if you have to export data frequently in different formats. Any recommendations on that without changing defaults?
    – Anyone
    Jul 12 '18 at 8:04
  • 2
    This is terrible. Then again - so it goes with many MS products. Nov 3 '18 at 5:03
  • 2
    This is ridiculous from Microsoft's part. If I commit a .csv file to a repository, how can I know if every user will be able to open that file correctly (with correctly recognized delimiter?). If my file has a ";" delimiter and the user's region expects "," the file is going to break. It doesn't help that SEP=; will be removed by excel on save Apr 28 '20 at 11:03
  • LibreOffice calc...when you go to Save-As type CSV, you have the option to pick export filters...for instance to customize the separator in the output. It is very common that a user will have one locale (e.g. Germans using ; separated CSV) but need to produce a CSV file for low-tech American SaaS that only accepts , separated CSV. Changing Excel's locale settings to produce this one file is not a reasonable option.
    – mattpr
    Jan 25 at 11:10
4

I don't see an option immediately in Excel, but I can offer some advice.

Remember that CSV files are Comma Separated Values, which follows a standard: Data, a comma, data, a comma, etc. To break this means it will not follow the specification.

What happens if you have a comma in your data though? Excel will automatically surround that cell's content with quotation marks ("), so the program importing the file will know when that cell actually ends.

If you do not want a comma as a separator, you can save the file as a CSV still, then edit it manually. Open a text editor like Notepad or Notepad++. Do a search for whatever character you want to use (i.e. in Notepad, go to Edit->Find). If you want a pipe, search for a pipe (|). If you hit any results, do not use this character. To search for a tab easily, I'd recommend using Notepad++.

Once you find that the character you want to use isn't being used elsewhere in your document, then you can do a Search and Replace. In Notepad, go to Edit->Replace.... In 'Find what:', type a comma. In 'Replace with:', type the character you want to use for the separator. The next part is to hit Find Next. The reason for this, is that if there are commas in your data as well, this will break your file. Only replace the commas that are delimiters of the data, and nothing in quotation marks.

Once this is done, you can save the file. If you want to import it, I'd recommend renaming it to a .TXT file, so Excel will ask you what the Field Delimiter Character is. If you choose to open a .CSV file, it will automatically use a comma as the delimiter, which is what you don't want.

2
  • 4
    There is no official spec for csv files. There are only conventions. CSV generally means comma separated, but data files using other delimiters are often referred to as CSV files. Dec 20 '16 at 18:06
  • 2
    You can also say that CSV stands for character separated values, it all depends on the decimal separator. Every language that does not use a dot as the decimal separator has to use something else than a comma to separate the values. That's why CSV files created with German language settings use a semicolon.
    – alpipego
    Nov 8 '17 at 7:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.