Set separator delimiter in CSV file to open correctly in Excel

Problem

I made a PHP script to export a SQL query (from MySql) to a CSV file for opening in Excel. I cannot control how different users have their Excel setup, ie what separator delimiter they have set (this differs with language settings or user may have changed setting)

So the field separator delimiter gave me some problems, ie Excel would not automatically open file columns correctly but put each row in a single cell, unless you use Import data to make it work.

Some forums suggested setting the separator delimiter in the csv file (delimiter is third argument for fputcsv) to tab (\t) but that did not help.

Solution (use csv header sep=)

Then I stumbled upon the possibility to add a parameter as a header to the actual csv file, simply add sep=<delimiter> to the beginning of your csv file and Excel will parse it. So for separator delimiter use comma (,), pipe (|), tab (\t) or whatever you set in your php code.

However I found a few caveats:

• “sep=\t” needs to be in quotes (otherwise it will use header but also for some reason also show it as data in the spreadsheet and put it in its own cell and move all other cells on that row one position to the right)
• You need to add newline \n after the sep= header (otherwise it will use header but also show it as data concatenated with first cells data)
• Use fwrite as fputcsv will not work

Example to use tab as separator:

[code]fwrite($fp, "\"sep=\t\"\n");[/code]

I could not find much info on the subject of csv headers but this is the full thread that lead me on the to it (thanks Burhan Ali)

Tested with Excel 2016 on Windows 10 and Excel for Mac (15.25) on OSX El Capitan (10.11.15).

I have not tested on other spreadsheets software like OpenOffice, LibreOffice etc, so please share in the comments if you have any success with this.