Question or issue on macOS:
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?
How to solve this problem?
Solution no. 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
Solution no. 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.
Solution no. 3:
Transporting iPad / iSpreadsheet doc to Mac OS 7 Excel doc
Email the iSpreadsheet doc to yourself as .csv file.
Open the .csv file in Numbers
Email the Numbers doc to yourself as an .xlsx file.
Open the attached .xlsx doc in Excel on your Mac OS 7.
Solution no. 4:
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.