PHP csv functions, Microsoft safe?

Question

Hi there,

I have been looking into the php fputcsv function, and have heard some comments that the files created using this method will not work on microsoft excel, is this correct?

Comment on php.net

since PHP is generally *nix-based, it makes sense that the line endings are always \n instead of \r\n. However, certain Microsoft programs (I'm looking at you, Access 97), will fail to recognize the CSV properly unless each line ends with \r\n. Secondly, if the first column heading / value of the CSV file begins with uppercase ID, certain Microsoft programs (ahem, Excel 2007) will interpret the file as being in the SYLK format rather than CSV, as described here: http://support.microsoft.com/kb/323626

So

If i am planning on outputting data in the format of

0, 0278221234, 60143512345, 5pt code, yes
1, 0278221234, 60143512345, 5pt code, yes
2, 0278221234, 60143512345, 5pt code, yes
3, 0278221234, 60143512345, 5pt code, yes

also,

what is the difference between writing a file using this function

and simply creating the line's as above and ending them with \r\n ?

Answer 1

IF you have problems with Excel, you can always replace \n with \r\n once the file is created. It's like 4 more lines of code.

[edit]

Just tested with Excel 2007. Opens fine.

Answer 2

files created using this method will not work on microsoft excel, is this correct?

This is partially correct. MS Excel can read the files fine when the user uses the import wizard. But you'll want that the files to work without user knowing how you've made them -- that is, without using the import wizard.

To make the files work automatically, use ; (semicolon) as a record separator. E.g.

$data = array("one", "two", "three");
$fh = fopen('test.csv', 'w');
fputcsv($fh, $data, ";");
fclose($fh);

Also note that in order to write UTF-8 to the CSV you'll have to add UTF-8 BOM to the beginning of the file.