Opening csv files in MicroSoft Excel on a mac

Question

I am having trouble opening csv files correctly in Microsoft Excel for Mac 2011, Version 14.1.0. The files will open but the dialog box to set the delimiter as a comma does not come up and thus all the columns are concatenated with commas. Is there a way to get Excel to recognize the comma delimiter?

Answer 1

The trick is that you have to create a new workbook and then import the CSV file (i.e., via File / Import). In the CSV import dialog you can select things like the delimiter, encoding, etc.; whereas, you don't have the option of setting the delimiter character, etc. if you just open the existing CSV file directly.

This is also discussed here: https://apple.stackexchange.com/questions/23220/how-to-open-csv-file-with-microsoft-excel

Answer 2

One way is to insert sep=yourdelimiter in the begining of the file.

If you generate the csv in PHP: fwrite($fileHandle, 'sep=yourdelimiter' . "\r\n");

Otherwise you can open your csv with a text editor and add sep=yourdelimiter to the begining of the file.