...

View Full Version : Help Needed with Exporting to a .CSV file



rfresh
11-21-2007, 12:03 AM
My .php script is exporting some data to a .csv file. The problem I'm having is displaying data that has multiple lines in it. To keep the linefeeds and CRs from screwing up the .csv file, I am presently replacing LF and CRs with [lf] and [cr]. I can now see these in the csv file when I open it in Excel.

I can't find a way to either leave one CR in or LF out (or any combination) so that when I open in Excel the data is on two lines in the same cell.

Presently my data in one cell looks like this:

This is a[cr][lf] test of[cr][lf] multiple lines.

And I want it to end up displaying like this in one cell:

This is a
test of
multiple lines.

Thanks for any help.

aedrin
11-21-2007, 12:34 AM
CSV has its limitations.

One being that you do not have control over what the information looks like.

You will either have to strip out the newlines (in text this is generally not a problem if it's used for human reading) or just deal with the fact that a newline in a CSV file will create additional rows.

You can't make the cell expand, or make it somehow hide the additional lines.

mlseim
11-21-2007, 12:35 AM
This is my guess ... but I didn't try it.
Comma delimited with a new line with the last cell.

Try this as an experiment and see what happens.
It will create and write to "csv.txt". Then import
into Excel and see what happens.



<?php
$name="John Doe";
$address="123 Elm Street";
$city="St. Paul";
$state="Minnesota";

$fp = fopen("csv.txt", "a+");
fwrite($fp,stripslashes("$name,"));
fwrite($fp,stripslashes("$address,"));
fwrite($fp,stripslashes("$city,"));
fwrite($fp,stripslashes("$state,\n"));
fclose($fp);
}

$name="Bill Anderson";
$address="755 Oak Ave.";
$city="St. Paul";
$state="Minnesota";

$fp = fopen("csv.txt", "a+");
fwrite($fp,stripslashes("$name,"));
fwrite($fp,stripslashes("$address,"));
fwrite($fp,stripslashes("$city,"));
fwrite($fp,stripslashes("$state,\n"));
fclose($fp);
}

?>


EDIT:
I see now I might have misunderstood the question.
Anyhow, someone else might find the example useful.

GJay
11-21-2007, 12:43 AM
php 5 has the the fputcsv function which will do all the hard work for you:


<?php
$data = array(
'some text', "some more text, \n this time with \n lots of \n lines", 'and some more'
);
$fp = fopen('/tmp/test.csv','w+');
$csv_line = fputcsv($fp,$data);
fclose($fp);


OpenOffice sees it as one csv-row with 3 columns the second one having more than one line of text, I'd imagine Excel would too.

mlseim
11-21-2007, 12:47 AM
oh, I forgot all about "fputcsv"

good one, GJay.

aedrin
11-21-2007, 01:30 AM
Yes, Excel does the same thing.

But all that hard work is actually automatic. I forgot that putting double quotes around the data fixes any formatting issues.

My statement stands, CSV does not allow you to manipulate the output.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum