17
4
Is there a way to tell Excel which delimiter to use when producing a CSV, e.g. tab or pipe?
17
4
Is there a way to tell Excel which delimiter to use when producing a CSV, e.g. tab or pipe?
17
Assuming we're dealing with Windows as your OS...
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.
3There 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 – 2016-12-20T18:06:23.347
1You 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 – 2017-11-08T07:41:08.510
1For tab separated files, you can just use the built in file format
Text (Tab delimited) (*.txt)
. – Richard – 2014-07-14T18:01:23.2101This is not a good solution if you have to export data frequently in different formats. Any recommendations on that without changing defaults? – Anyone – 2018-07-12T08:04:53.920
This is terrible. Then again - so it goes with many MS products. – javadba – 2018-11-03T05:03:46.670