Opening a CSV file created by PHP

Question

I create a CSV file using fputcsv in PHP. File is created successfully and I can open the file in MacOS with no problem. (I use Numbers in MacOS). The problem is in Microsoft Excel, it shows all row as merged one single column.

I set delimiter as ";" in the code.

When I check for Language and Regional Settings as told in Microsoft documentation, the delimiter is also ";".

What should I also check for? Thank you.

Different Line-break in MacOs and Win \r\n vs. \n can be the issue. Defaults are (as i know): Win \r\n Linux \n Mac \r — JustOnUnderMillions
– JustOnUnderMillions, Commented Jan 30, 2017 at 11:00
Did you set the column delimiter to ; or the line delimiter to ;? — RiggsFolly
– RiggsFolly, Commented Jan 30, 2017 at 11:03
Are you talking about opening the csv on MAC Excel or Windows Excel? — RiggsFolly
– RiggsFolly, Commented Jan 30, 2017 at 11:19

ben · Accepted Answer · 2017-01-30 11:22:16Z

3

Well this header will allow the csv format to displayed properly.

 header('Content-Encoding: UTF-8'); header('Content-type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename=filename.csv'); echo "\xEF\xBB\xBF";

You can use this header

edited Jan 30, 2017 at 11:22

answered Jan 30, 2017 at 11:03

ben

4703 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RiggsFolly Over a year ago

Can you add an explanation of why this will solve a problem opening a .csv file please?

ben Over a year ago

stackoverflow.com/questions/4348802/… u can refer this

RiggsFolly Over a year ago

Then maybe you should have suggested that this question has a duplicate

RiggsFolly Over a year ago

ALSO: Why should the OP "use this code"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer. They may also then feel inclined to give it an upvote

Peter · Accepted Answer · 2017-01-30 11:16:36Z

The following method seems to do the trick for me. Microsoft Excel opens it perfectly.

$filePath = '/home/user/'; $filename = 'test.csv'; $df = fopen($filePath.$filename, 'w'); fprintf($df, chr(0xEF).chr(0xBB).chr(0xBF)); fputcsv($df, $dataColumns); foreach ($dataArray as $dataRow) { fputcsv($df, $dataRow); } fclose($df); // Output csv header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename='.$filename); header("Cache-Control: no-store, no-cache"); readfile($filePath.$filename);

Notes

the line fprintf($df, chr(0xEF).chr(0xBB).chr(0xBF)); writes file header for correct encoding.
You can use the third parameter of fputcsv(...) to set the delimiter.

It may well open it perfectly for you. MS Excel is locale-specific about the separator uses; what works for one person in one locale will not necessarily work for another person in another locale
While that is true, EF BB BF is the BOM (simple.wikipedia.org/wiki/Byte_order_mark) which has nothing to do with locale.

Collectives™ on Stack Overflow

Opening a CSV file created by PHP

2 Answers 2

4 Comments

Notes

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Notes

2 Comments

Linked

Related