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.
