1

Using PHP to extract data from SQL and then creating a .csv file on the server for emailing/download by way of fputcsv. All works well other than trying to get a new line within a field in Excel (2003).

I get Product1Product2Product3 in the cell when I need

Product1
Product2
Product3

I have tried single quotes, double quotes, CRs, LFs and I am rapidly disapearing up my own backside.

So the question is - what character do I need to get into the CSV file to achieve this?

It has to work in Excel

[Solution] - the problem lay somewhere in passing the \r\n to Excel through the PHP fputcsv - I was unable to achieve this in any sort of fashion (plenty of appending the desired \r\n to my actual cell data e.g. Product1\r\n) The suggestion to use $lfcr = chr(10) . chr(13); worked first time around. I guess this was more of a PHP rather than an Excel question - thanks to all resonses.

vascowhite
  • 18,120
  • 9
  • 61
  • 77
  • I'm almost sure you tried it, but just to be more sure, have you tried the "\n"? – Aurelio De Rosa Oct 14 '11 at 09:38
  • It seems there is already an answer to your problem there : [Generating CSV file for Excel, how to have a newline inside a value][1] [1]: http://stackoverflow.com/questions/1241220/generating-csv-file-for-excel-how-to-have-a-newline-inside-a-value – Aweb Oct 14 '11 at 09:42
  • @Aweb - been at that one already –  Oct 14 '11 at 10:02
  • @Aurelio - yes tried \n and \r\n –  Oct 14 '11 at 10:03

2 Answers2

6

This is more of an excel rather than a php question. What you put into the csv file needs to be understood by excel which is why \r\n will not work.

Use this

$lfcr = chr(10) . chr(13);

Then append $lfcr to the end of each line.

vascowhite
  • 18,120
  • 9
  • 61
  • 77
  • Works great! I used `$item = str_replace("\n", chr(13), $item);` to replace the newlines before using fputcsv. Using the two chars (10 and 13) resulted in two newlines inside the Excel cell – Felipe Balduino Cassar Jul 31 '13 at 17:41
1

for linebreaks in a field in a csv-file you just have to surround the field with double quotes and prepend a = like in the following example. the linebreaks itself can be either \r\n or just \n:

id;product;price
1;iMac;2.99
2;="product
with
linebreaks";1.99
3;Bananaphone;999.99
oezi
  • 51,017
  • 10
  • 98
  • 115
  • Bananaphone sounds good to me too - thanks for the response - see the solution I found from Vascowhite - I was not able to get the \r\n to precolate through the PHP function into the actual csv file –  Oct 14 '11 at 10:09
  • hm, a downvote without a comment explaining the reason. this is helpful. – oezi Sep 05 '12 at 06:34
  • Sorry, yes, I should have explained the downvote. It was because your answer is wrong :) – vascowhite Jul 31 '13 at 19:58