1

I am generating a csv from an array of arrays using fputcsv() . One of the field contians a html code snippet and it is causing problems when I open it in Excel.

When opened in excel , it breaks across multiple cells .

Please note that in the fputcsv I have used , as the delimiter and " as the enclosing element.

Still I could see that the html string was being broken across multiple cells , and this was occuring because of commas inside the htmlcontent .

So , how can I exactly escape the inlying commas ?

For the time being , I did replace the commas with whitespace . But , still I am seeing that a long htmlcode (nearabout 53823 characters) breaks across multiple cells when opened in MS Excel . Is there any upper limit to the number of characters that a single cell in Excel can contain?

Please note that when I open my csv in Notepad++ , it shows that the CSV is well formed without any breaks in between. However , my client uses MS Excel , and it's kind of hard convincing him that the CSV is actually well formed . Please help.

Here's the code that I have been using :

//escape the newline characters
$template = str_replace(array("\r\n", "\n", "\r", "\t"), '', $template);
$cache = str_replace(array("\r\n", "\n", "\r", "\t"), '', $cache);

$fh = fopen('abc.csv', 'a');
fputcsv($fh,array($template,$cache),',','"');

the $template and $cache are having html code snippet as field values.

Also , what I noticed is that if I don't escape the , , the string breaks across multiple cells . For this I have tried to str_replace(",","\,",$string) but to no use.

Update : The issue is in cases where both quotes and comma occur inside a string . e.g consider a string I have "big" quesiton, that I want to know.

"I have "big" quesiton" comes in one cell and "that I want to know." comes in another cell instead of the entire string occuring in one column.

deGee
  • 781
  • 1
  • 16
  • 34
  • can you show your code ?? – Php Geek Feb 18 '13 at 10:36
  • Does your HTML contain double-quotes `(")`? If so you should escape them using backslash `(\)` – andho Feb 18 '13 at 10:37
  • Did a quick search, and it seems fputcsv actually does the escaping for you. http://stackoverflow.com/questions/6325613/escaping-for-csv So you need to post code to get any further with this issue. – andho Feb 18 '13 at 10:39
  • Tested a short HTML snippet with `fputcsv` under PHP 5.3.10 and opened with Excel 2003, it works fine. Try to chop down your suspiciously long HTML code to a much shorter one to test. – Passerby Feb 18 '13 at 10:47

1 Answers1

0

It is better to use a free PHP excel exporter class by Eli Dickinson, I was born again when starting using it, no problems now whatsoever when generating and opening the files on client`s side. Here is the link: http://github.com/elidickinson/php-export-data

Bud Damyanov
  • 30,171
  • 6
  • 44
  • 52