How can I strip all line breaks to generate a proper CSV?

Question

I have a textarea submitting to my database on a website that is properly working. But when I generate a CSV (via PHP) from my database, all line breaks will mess up with the resulting CSV. Any CSV reader will interpret the line break from the input into a new line.

I have tried the following approaches:

1. Encapsulating the fields in quotation marks.

2. This:

   $field = str_replace(array('\n', '\r', '\r\n', '\n\r'), ',', $original_field);
   

3. Also this:

   $field = strip_tags(nl2br($original_field));
   

4. Combining all approaches above.

Anyhow, the ending result will still be a messed up CSV that will break on any line break inputted by user. I have managed to block new line breaks from the text area, but there's a lot of legacy submissions that need me to fix this on the CSV side as well.

Why is it not working? How can I fix this issue?

Answer 1

Before accepted answer (of user user1517891) is not correct, it will replace in string twice, when there is \r\n... It will replace it as two commas ,. First it will replace \r => ,, then \n => ,.

You need to use it in different order, as:

$field = str_replace(array("\r\n", "\n\r", "\n", "\r"), ',', $original_field);

Answer 2

Use double quotes:

$field = str_replace(array("\n", "\r", "\r\n", "\n\r"), ',', $original_field);

Answer 3

I'd suggest using preg_replace() for this rather than str_replace(). The reason is that there may be multiple newlines and combinations of \r and \n, and I would expect that you'd want to replace them all with just a single comma.

I'd also suggest using trim() to remove trailing blank lines.

$field = preg_replace('/[\n\r]+/', ',', trim($original_field));

Answer 4

You have to put \n and similar tags in double quotes otherwise they will be treated as simple strings and not as linebreaks.