Correct exporting database into Excel

[eluser]Jackpod[/eluser]
When using...

Code:

force_download('filename.tsv',$this->dbutil->csv_from_result($query));

...the downloaded file does not correctly import into Excel if there are carriage returns within the data in the database. These carriage returns were submitted through textarea form fields. The returns stored in the database mess with the format of the text file.

I am sure there is a way to format this text file to handle the returns so it will properly import into Excel, but I do not know what to do.

Can someone please advise me? Thanks so much!

[eluser]pims[/eluser]
I may have one suggestion:

of course, it depends of the structure of your database, but i'd export it as xml and then use an xslt stylesheet to convert it to an excel xml format (you can strip down a lot of unecessary tags). Set the appropriate header, and you should be good to go.

hope this helps

[eluser]Jackpod[/eluser]
Hi - Thank you for your reply. I can give that a try, but it will take me some time to learn about how to do that. I was hoping for just a quick way to encode the return characters properly. Anyone else have an idea of the proper encoding?

[eluser]mazaka[/eluser]
Did anyone find a good solution for this without using XML?
It would be nice if csv_from_result would take a parameter for "remove line breaks in data" btw.

[eluser]jedd[/eluser]
I think you write your own PHP function that takes the output of the db_util->get csv stuff, and massages the data properly.

You could check out the relevant bit in the user guide - [url="http://ellislab.com/codeigniter/user-guide/database/utilities.html"]http://ellislab.com/codeigniter/user-guide/database/utilities.html[/url] - where they have this enticing comment to make:

The second and third parameters allows you to set the delimiter and newline character. By default tabs are used as the delimiter and "\n" is used as a new line. Example:

Code:

$delimiter = ",";
$newline = "\r\n";

echo $this->dbutil->csv_from_result($query, $delimiter, $newline);

But I suspect that's not an 'interpret existing intra-record line breaks' so much as 'I'm a MS / OSX / Linux user' option.

It does mean you could set the NL string in there to something quite out of the ordinary, and then your PHP script could more safely hunt through the resultant data for newlines, which would presumably then only be the intra-record ones you want to rid yourself of, and then the last thing you do is one last sweep, replacing the 'out of the ordinary' string with a real newline .. before outputting your data.