66

I generate a CSV file with an extension .csv in which every piece of data in one line is separated with a comma:

1,2,3,4
1,2,3,4

The file is sent via email and I want that when a customer opens it she sees data arranged into columns.

Is such thing possible?

PS: I may freely change the delimeter.

Improve this question
5
  • 1
    As far as I am concerned, you can't do anything except for importing the data into Excel, since Excel can't make any assumptions about the structure of the data itself (e.g. the separator used, the format of decimals or the general format of data).
    – slhck
    Jan 28, 2011 at 14:32
  • @slhck please look at the answer
    – Timofey
    Jan 28, 2011 at 14:39
  • Oh, I didn't know that Excel was capable of doing this. Nice one.
    – slhck
    Jan 28, 2011 at 14:41
  • 1
    By just double click the CSV file, Excel will be opened and the file will be displayed correctly. Am I correct?
    – wilson
    Jan 31, 2011 at 6:17
  • @wilson: Excel has to be associated to open .csv files then by double clicking a .csv file excel will be launched
    – Timofey
    Jan 31, 2011 at 16:33

9 Answers 9

Reset to default
16

Use tab instead of comma. And if that doesn't work, give your tab-delimited file an xls extension.

Improve this answer
4
  • doesn't work for excel 14.7.1 on the mac (2017)
    – masukomi
    Jan 11, 2017 at 18:11
  • 5
    the "PS: I may freely change the delimeter." makes this answer less valid
    – Paul Fijma
    Oct 11, 2018 at 7:25
  • 3
    This won't answer how to load a CSV file, since C stands for comma.
    – JBernardo
    Aug 30, 2019 at 15:22
  • 1
    @JBernardo It did, once, but today you'll find files with csv extensions in the wild with all kinds of delimiters: tab, pipe, comma, dash, @, \0, etc. Right or wrong, they're out there, and the part of the question saying "I may change delimiters" makes this answer valid.
    – Joel Coehoorn
    Aug 30, 2019 at 15:39
95

Sadly, Microsoft decided to use different separators in different localizations for CSV (which stands for Comma Separated Values). That's especially annoying when working on an international basis.

If it's only to get the csv readable at every system, there's an undocumented trick: in the first line write SEP=;. This line tells EXCEL to use ; as separator (in fact you can use any (single) character (;,|@#'...))

NOTE: this line is just part of the csv file itself. It will not become part of the spreadsheet in EXCEL. that means, it will not be shown and it will not be written, no matter which format you define to write or export.

NOTE: this is Microsoft EXCEL specific. Other spreadsheet programs may not understand this.

Improve this answer
3
  • 3
    This breaks utf character support for me. Chinese and russion characters are shown incorrectly.
    – Sámal Rasmussen
    Jan 20, 2020 at 13:23
  • That was good hack, thank you!
    – T1000
    Feb 27, 2022 at 18:20
  • Thanks for this! Maybe someone should inform the maintener of Export-Csv...
    – PRouleau
    Oct 18, 2022 at 2:36
62

The behavior of Excel when opening CSV files heavily depends on your local settings and the used list separator under Region and language » Formats » Advanced. By default Excel will assume every CSV was saved with that separator. Which is true as long as the CSV doesn't come from another country!

If your customers are in other countries, they may see other results then you think.

For example, here you see that a German Excel will use semicolon instead of comma like in the U.S enter image description here

To confuse you even more, that setting interferes with the decimal symbol which can be separately set up under Excel Options » Advanced » Use system separators or via regional settings as shown above. Excel can't use the same symbol as decimal tab and list separator. It will automatically use comma or semicolon as a backup separator. Read more

I will take your example to create 3 files. Each with a different separator and open it with Excel.

    COMMA               SEMICOLON                     TAB

enter image description hereenter image description hereenter image description here

Not the same as your Excel does? I thought so.

So lets manually change the extension from the same CSV files to XLS and look what happens now. First, Excel will throw up a warning that the file extension doesn't match the content and Excel tries to guess whats inside. 

    COMMA        SEMICOLON                   TAB

enter image description hereenter image description hereenter image description here

Conclusion: TAB + renaming + ignore warning = Win on every system?
Maybe, but I wouldn't be so sure for customers outside your own country.

The best method is to instruct your customers to first open a blank Excel and then go to Data » Get data from text and manually select comma as separator

Improve this answer
4
  • 10
    This answer is great but I find this pathetic from Microsoft. You open a csv in Numbers and it opens correctly. You open it in Google sheets and it opens correctly. Then you open it in Excel which is the only one you pay for and... Nothing. What about those of us that have never set a list separator? Now I need to waste time trying to make it work because "commas" mean nothing in this "csv". Very frustrating.
    – Touch
    Feb 14, 2019 at 8:14
  • > This solution works to me (excel 2013). I adjust my regional format: I set a dot (.) as "decimal separator", a comma (,) as "thousand separator" and a comma (,) as a "list separator" – cesargastonec Jul 13 '15 at 16:05
    – Alex78191
    Nov 30, 2019 at 15:47
  • Or you can change decimal separator in Excel options superuser.com/a/1470373
    – Alex78191
    Nov 30, 2019 at 15:53
  • Thanks! This should be the answer if your idiom is different from English US.
    – Eduardo Rosostolato
    Jan 27, 2023 at 14:59
3

You can replace ".csv" by ".txt", start Excel and open that .txt file. Then Excel will show you an assistant where you can specify to use "," as seperator. When you then save this file as .csv again, the next time Excel will open it the way you need it.

Improve this answer
0

Another solution that I used in this situation, was to output a basic HTML file, instead of CSV. Excel can open this without an error. It can read at least some of the formatting in the file too.

Improve this answer
0

This is the dumbest answer ever, but it worked. I use CSV files daily. I create them all the time and view with excel. Today, after 12 years of never seeing this problem, it happened to me. It wouldn't open a csv file and format the columns. Even some csv files I created last night with excel, would open into one column per line.

Solution: Check task manager for all open instances of Excel.exe. Kill all instances, and try to open the csv file again.

facepalm

Improve this answer
0

In the short term if you only need to open a CSV file that has been sent to you from another country -

Open the file in wordpad and use find and replace to change every separator the issuing country has used (eg. ;) and replace with a comma. (eg ,).

Just check the issuing country does not use a comma in place of another symbol (eg in Spain they use a comma where we use a decimal place so you would have to find and replace the comma to a decimal place then find and replace the other symbol with the comma).

Not a long term solution but good enough to make the file itself readable if you are in a hurry and can't access the control panel at work.

Improve this answer
0

I came over the same issue. I had CSV file that Excel was parsing wrongly and needed the Import Wizard.

Simple fix was to rename CSV to TXT and the Wizard came up helping to recognize the column structure properly.

Improve this answer
0

An answer related to this -- especially for MacOS / OSX problems with Excel recognising commas:
in Excel 2019 not opening CSV correctly - not parsing columns: https://superuser.com/a/1506042/1116939

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .