Outputting a CSV file

problem with line breaks

[HelenDev]

I've been searching on here for information about how to generate a CSV file and found this post

[webmasterworld.com ]

I tried this code which works insofar as it generates the file

<?php
$filename = 'whatever.csv';

$csv_output = ' "ID", "NAME", "EMAIL" \n';

$csv_output .= ' "1", "John Doe", "[e-mail]" \n';

$csv_output .= ' "2", "Jane Doe", "[e-mail]" \n';

header("Content-type: application/x-msexcel");
header("Content-disposition: attachment; filename=".$filename);
header("Pragma: no-cache");
header("Expires: 0");

print $csv_output;
exit;
?> 

But unfortunately when I open the file in Excel, I just get all the info on one line, albeit in seperate cells. When I open the csv file in notepad I get this, which doesn't look right either - I think there is a problem with the line breaks?

"ID", "NAME", "EMAIL" \n "1", "John Doe", "[e-mail]" \n "2", "Jane Doe", "[e-mail]" \n

Can anyone advise? Thanks.

[rocknbil]

Well, first, the single quote will not interpolate \n, it has to be "\n":

$csv_output .= ' "2", "Jane Doe", "[e-mail]" ' . "\n";

But you may stil have problems with some downloads and imports, I have experienced (or my clients have) the same thing in perl. You can try using an octal character instead of \n or \r, which will create O.S. specific newlines/carriage returns. Untested in PHP, but if it works it's an excellent habit to form as it heads off a lot of problems when working on various platforms.

$CRLF = "\x0d\x0a";

.....

$csv_output .= ' "2", "Jane Doe", "[e-mail]" ' . $CRLF;

[HelenDev]

Excellent, thanks rocknbil :)

Both these methods work, and I will use your octal character method, as I'm sure you know lots more than I do about this stuff!

[whoisgregg]

I've gotten in the habit of using the constant PHP_EOL for all line breaks. I'm not sure if it solves the OS specific issue though.

[rocknbil]

^ ^ ^ Being a PHP topic, and my approach is more grow-your-own, whoisgregg's solution is probably more correct. :-)

[penders]

... whoisgregg's solution is probably more correct. :-)

Not necessarily, as whoisgregg states, "not sure if it solves the OS specific issue". PHP_EOL returns the OS specific end of line marker for the server in which the PHP engine is running. Not the local machine. So this will be "\n" for Linux, "\r\n" for Windows" and "\r" for Mac (I believe). If you are generating a txt/csv file that you know a Windows user is going to be downloading via their browser then you might want to return "\r\n" regardless of the server OS. Just a thought.

This might not matter in HelenDev's case if the user chooses to go straight to opening it in Excel. But if they save it and open it in Notepad then it will be a problem (if PHP_EOL is used on a Linux server and the user is running Windows).

You can try using an octal character instead of \n or \r, which will create O.S. specific newlines/carriage returns.

I don't think this is OS specific (perhaps language specific?). AFAIK "\r" is always a carriage return (0x0D (13) in ASCII) and "\n" is always a linefeed (0x0A (10) in ASCII) regardless of OS, so "\r\n" is identical to "\x0d\x0a" (hexadecimal equivalent) - in PHP at least.

[rocknbil]

I learned that trick on a project years ago and like many things, what works is what works so I forget other approaches. :-) I do recall that on that particular project I tried all permutations of \n, \r, \r\n, and \n\r, none of which solved it for the client.

A caveat here, it may make a difference as to whether you are saving the data to a file or outputting to the browser for download. Sometimes one will give you a different result than the other.

Most of the time a simple \n will do the trick but when you get in trouble . . .

[penders]

Most of the time a simple \n will do the trick but when you get in trouble . . .

Yes, I'd agree. And any editor (even Windows) that's worth its salt should at least be able to handle \n line endings. It's just that Windows favourite - Notepad - doesn't! (So beware, "When I open the csv file in notepad...")

[HelenDev]

Cheers for all the advice guys. It looks like people have been pasting stuff with line breaks into my database, and then the CSV file is getting screwed up by starting new rows each time.

Is it possible to stop this happening but still preserve the line breaks in the CSV file? I guess that I also need to escape any commas in the inputted text.

[HelenDev]

Ah, I realised that I wasn't using quotes properly in my code, now seems to be sorted.