How to force Excel to open CSV files with data arranged in columns

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.

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}

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 

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 

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

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