16

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

15

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

  • 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
  • 1
    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
  • This is terrible. Then again - so it goes with many MS products. – javadba Nov 3 '18 at 5:03
3

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.

  • 3
    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. – dusktreader Dec 20 '16 at 18:06
  • 1
    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.