Opening csv files in MicroSoft Excel on a mac

9
votes

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?

4
macosexcelcsv
Are you also the person creating the .csv file? Or simply been given the file? I ask because when you save a .csv in Excel you have the option of saving as CSV (Comma delimited) (.csv) CSV (Macintosh) (.csv) CSV (MS-DOS) (*.csv) - hardba11
change the file extension to .xls and try again. - gdelfino

4 Answers

13
votes

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

12
votes

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.

0
votes

Transporting iPad / iSpreadsheet doc to Mac OS 7 Excel doc

  1. Email the iSpreadsheet doc to yourself as .csv file.

  2. Open the .csv file in Numbers

  3. Email the Numbers doc to yourself as an .xlsx file.

  4. Open the attached .xlsx doc in Excel on your Mac OS 7.

0
votes

Open MS Excel with an empty workbook (!) - it's the most important part I suggest. Select "Import" (from menu) and follow instructions (CSV, delimiters so on). It will give the correct list with data.