1

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.

tolga
  • 2,462
  • 4
  • 31
  • 57

2 Answers2

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

ben
  • 470
  • 3
  • 11
  • Can you add an explanation of why this will solve a problem opening a .csv file please? – RiggsFolly Jan 30 '17 at 11:04
  • http://stackoverflow.com/questions/4348802/how-can-i-output-a-utf-8-csv-in-php-that-excel-will-read-properly u can refer this – ben Jan 30 '17 at 11:08
  • Then maybe you should have suggested that this question has a duplicate – RiggsFolly Jan 30 '17 at 11:10
  • 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 – RiggsFolly Jan 30 '17 at 11:10
3

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.
Peter
  • 8,776
  • 6
  • 62
  • 95
  • 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 – Mark Baker Jan 30 '17 at 11:09
  • While that is true, EF BB BF is the BOM (https://simple.wikipedia.org/wiki/Byte_order_mark) which has nothing to do with locale. – Herbert Van-Vliet Aug 29 '22 at 09:54