[RESOLVED] PHP created csv file has all the data in one Excel cell

bloody_legs

Using php I am converting a mysql table into a csv file. Then offering the user the option of opening or saving the file. All is good except that all of the data appears in the first cell of the Excel spreadsheet when the file is opened. The values are all enclosed in "" 'sand separated by ,'s. The code is shown here.

Thank you in advance for any help you can give me.

Cheers

<?php  

$table = "data_export";  

$result=mysql_query("select * from $table");  

$out = '';  

// Get all fields names in table  

$fields = mysql_list_fields("my_db",$table);  

// Count the table fields and put the value into $columns.  

$columns = mysql_num_fields($fields);  


// Put the name of all fields to $out.  

for ($i = 0; $i < $columns; $i++) {  

$l=mysql_field_name($fields, $i);  

$out .= '"'.$l.'",';  

}  

$out .="\r\n";  

// Add all values in the table to $out.  

while ($l = mysql_fetch_array($result)) {  

for ($i = 0; $i < $columns; $i++) {  

$out .='"'.$l["$i"].'",';  

}  

$out .="\r\n";  

}  

// Open file export.csv.  

$f = fopen ('export.csv','w');  

// Put all values from $out to export.csv.  

fputs($f, $out);  

fclose($f);  

header('Content-type: application/csv');  

header('Content-Disposition: attachment; filename="export.csv"');  

readfile('export.csv');  

?>

Thanks again

BIOSTALL

I can't immediately see the issue but one thing I would try is change:

$out .='"'.$l["$i"].'",';

To:

$out .= '"' . str_replace('"', '""', $l["$i"]) . '",';

It could be that one of the values you are writing contains a double quote and therefore results in an odd number of double quotes.

Let me know if that doesn't work.

BIOSTALL

bloody_legs

Biostall,
Thanks for your effort, however, this did not change anything. FYI, I am pretty familiar with the data as I input it myself in order to test site I am working on. I am pretty sure there are not any values that have a double quote.
Cheers
Bloody Legs

NogDog

I'd recommend using [man]fputcsv/man, something like this (but with better error-handling 😉 ):

<?php
mysql_connect('localhost', '****', '*****') or die('connect');
mysql_select_db('book_list') or die('select');
$result = mysql_query('SELECT * FROM author') or die('query');
if(mysql_num_rows($result) == 0)
{
   die('no data');
}
$file = uniqid('', true).'.csv';
$fh = fopen($file, 'w') or die('fopen');
$cols = array_keys(mysql_fetch_assoc($result));
fputcsv($fh, $cols);
while($row = mysql_fetch_assoc($result))
{
   fputcsv($fh, $row);
}
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="download.csv"');
header('Content-Length: ' . filesize($file));
readfile($file);
unlink($file);
exit;

bloody_legs

Hey NogDog

This solution is very close. It does have two bugs. Before I start I am quite new to PHP code so I may be missing something that is obvious. Your help is super and I thank you for it.

SO #1
The test table I set up has 9 records.
The file we save using your code, when opened displays the header row correctly and then missing the first record, then displays the other 8.

#2
This is related to the line of code - "unlink($file);
After the last record is printed I see the following error message
 
Warning: unlink(export.csv) [<a href='function.unlink'>function.unlink</a>]: Permission denied in D:\Inetpub\wwwroot\InvMgr\export_2.php on line 33

I commented out the line in question. Does this create a issue of some sort by not unlinking. I was unable google up and explanation for this function that seemed to lead to my understanding. I have done a lot of this sort of thing using asp and I do not recall needing to formally unlink for a file.

Thanks much NOGDOG
Bloody Legs

Weedpacket

1) A typo on NogDog's part: he fetches the first row in order to get the keys (for the column headers), but doesn't output its values. Assuming you can't know what the fields are (and so put them in an array to start with, and use them in the SELECT statement), then [man]mysql_list_fields[/man] will get a list of fields in a given table.

2) You'll end up with temp files filling your filesystem if you don't delete them (incidentally, [man]tmpfile[/man] does the job of creating a temp file and should handle deleting it when it's finished with).

You could also avoid using the temp file completely by writing to the STDOUT handle

fputcsv(STDOUT, ...

You'll need to output the headers first, of course. The down side is that these headers include the Content-Length, and obviously that isn't known until the content has been created. That apparent catch-22 can be avoided by buffering the output.

ob_start();
// write the CSV, using STDOUT as the resource handle to write to
$content_length = ob_get_length();
// Headers
ob_end_flush();

NogDog

Here's an updated version with a mysql_data_seek() added so that the while loop gets all result rows, and using Weedpacket's tmpfile() suggestion.

<?php
mysql_connect('localhost', 'xxxxx', 'xxxxx') or die('connect');
mysql_select_db('book_list') or die('select');
$result = mysql_query('SELECT * FROM author') or die('query');
if(mysql_num_rows($result) == 0)
{
   die('no data');
}
$fh = tmpfile() or die('tmpfile');
$cols = array_keys(mysql_fetch_assoc($result));
fputcsv($fh, $cols);
mysql_data_seek($result, 0); // set result row pointer back to first row
while($row = mysql_fetch_assoc($result))
{
   fputcsv($fh, $row);
}
rewind($fh);
$text = fread($fh, 999999);
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="download.csv"');
header('Content-Length: ' . strlen($text));
echo $text;
exit;

bloody_legs

Thanks for your help

In general I am impressed that there are folks that are willing to help out on projects they are not a part of being done by people they do not know. First time I have tried using such a site. The experience was, satisfying and humbling and best of all successful.

Weedpacket.
I tried your solution, but I was unable to get it to work. I would like to think that it was because it was late and I was tired. Actually I think it was because I did not have enough expertise to apply your solution. Since I am self taught and new at this, I will keep studying. Thanks for your time.

NogDog,
Your revised code works perfectly. Thanks for revisiting.

Joy and laughter all
Bloody Legs