Excel save-as CSV options -- possible to change comma to pipe or tab instead?

Question

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

Solution 1

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.

• 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.

More info/image source

Solution 2

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.

Comments

[dusktreader]

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.

[alpipego]

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.

[Anyone]

This is not a good solution if you have to export data frequently in different formats. Any recommendations on that without changing defaults?

[WestCoastProjects]

This is terrible. Then again - so it goes with many MS products.

[walnut_salami]

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

[mattpr]

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]

I found this online CSV delimiter-changer tool for one of our non-technical users that is stuck using Excel and can't install LibreOffice just for this use-case. Keep in mind, simple (non-regex) search and replace may break your CSV if you have quoted values that contain the delimiter character. Please also don't use online tools for any sensitive/private/customer data. onlinecsvtools.com/change-csv-delimiter