sulentho
asked on
force download as csv
I am trying to force results from a mysql table as a csv and I am having issues as the format is all messed up. if i do the same thing to a .xls file it comes out perfectly. Please tell me what I am doing wrong. Thanks
<?php
ob_start();
session_start();
include ("includes/db.php");
include ("includes/functions.php") ;
$storeconn = dbconn(2);
$sql = stripslashes($_POST['sql'] );
$file = $_POST['file'];
$export = mysql_query($sql);
$fields = mysql_num_fields($export);
for ($j = 0; $j < $fields; $j++) {
$header .= mysql_field_name($export, $j) . "\t";
}
$cnt = 0;
while($row = mysql_fetch_row($export)) {
if ($cnt > 0) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$cnt++;
}
$filename = $file.".xls";
header("Content-type: application/octet-stream") ;
header("Content-Dispositio n: attachment; filename=".$filename);
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
mysql_close($storeconn);
ob_flush();
?>
<?php
ob_start();
session_start();
include ("includes/db.php");
include ("includes/functions.php")
$storeconn = dbconn(2);
$sql = stripslashes($_POST['sql']
$file = $_POST['file'];
$export = mysql_query($sql);
$fields = mysql_num_fields($export);
for ($j = 0; $j < $fields; $j++) {
$header .= mysql_field_name($export, $j) . "\t";
}
$cnt = 0;
while($row = mysql_fetch_row($export)) {
if ($cnt > 0) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$cnt++;
}
$filename = $file.".xls";
header("Content-type: application/octet-stream")
header("Content-Dispositio
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
mysql_close($storeconn);
ob_flush();
?>
ASKER
I made the following changes to the script and it gets the result into the csv as required except for a couple of rows where it messes up the format. I think this is because one or 2 of the fields have text with certain characters in there which is splitting the text into multiple columns rather than one column.
<?php
ob_start();
session_start();
include ("includes/db.php");
include ("includes/functions.php") ;
$storeconn = dbconn(2);
$sql = stripslashes($_POST['sql'] );
$file = $_POST['file'];
$result = mysql_unbuffered_query($sq l) or die ('CSV-query failed');
$rows = 0;
$csv = '';
while($row = mysql_fetch_assoc($result) ) {
if($rows++ == 0) {
$csv .= '"'.implode('","', array_keys($row)).'"';
}
$csv .= "\r\n".'"'.preg_replace("/ [\n\r]/",' ', implode('","', $row)).'"';
}
$file = $_POST['file'];
$filename = $file.".csv";
header("Content-type: text/csv");
header("Content-Dispositio n: attachment; filename=".$filename);
echo $csv;
mysql_close($storeconn);
ob_flush();
?>
<?php
ob_start();
session_start();
include ("includes/db.php");
include ("includes/functions.php")
$storeconn = dbconn(2);
$sql = stripslashes($_POST['sql']
$file = $_POST['file'];
$result = mysql_unbuffered_query($sq
$rows = 0;
$csv = '';
while($row = mysql_fetch_assoc($result)
if($rows++ == 0) {
$csv .= '"'.implode('","', array_keys($row)).'"';
}
$csv .= "\r\n".'"'.preg_replace("/
}
$file = $_POST['file'];
$filename = $file.".csv";
header("Content-type: text/csv");
header("Content-Dispositio
echo $csv;
mysql_close($storeconn);
ob_flush();
?>
might want to just remove the commas from the field values prior to imploding into your row:
$csv .= "\r\n".'"'.preg_replace("/ [\n\r]/",' ', implode('","', str_replace(',','',$row))) .'"';
Of course, those commas might be important, so maybe you need to just replace them with something like a semicolon - comma-like, but not going to confuse your csv-reading program:
$csv .= "\r\n".'"'.preg_replace("/ [\n\r]/",' ', implode('","', str_replace(',',';',$row)) ).'"';
$csv .= "\r\n".'"'.preg_replace("/
Of course, those commas might be important, so maybe you need to just replace them with something like a semicolon - comma-like, but not going to confuse your csv-reading program:
$csv .= "\r\n".'"'.preg_replace("/
ASKER
I need the commas to be in there in the text in those columns because they are product descriptions so have to have a work around, is there nothing that can be done to remedy my first bacth of code because that saves it perfectly as an .xls and i have to then open it in excel and save it as a csv which is frustrating. Please any help will be appreciated. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Its not working I am sending you a sample of the csv getting downloaded and you can see how certain rows are messed up because of the commas in the text.
this is the link to the csv
http://dev3.louder-inc.com/faulty_products.csv
Thanks and I really hope you can solve my dilemma
this is the link to the csv
http://dev3.louder-inc.com/faulty_products.csv
Thanks and I really hope you can solve my dilemma
ASKER
Open the file sent in excel and you will see what I am referring to. Thanks
That's so strange! I thought all the csv interpreters understood backslash to escape double quotes!
I did some research on the internet and discovered that for the most part I'd be correct... except for the 900-lb gorilla called Excel.
http://discuss.joelonsoftware.com/default.asp?pg=pgDiscussThread&ixDiscussTopicParent=1282&ixDiscussGroup=3&cReplies=11
The link there explains how someone did an experiment with Excel export to csv and discovered that apparently Excel uses two double-quotes together to signify an embedded quote.
So...
$csv .= "\r\n".'"'.preg_replace("/ [\n\r]/",' ', implode('","', str_replace('"','""',$row) )).'"';
I did some research on the internet and discovered that for the most part I'd be correct... except for the 900-lb gorilla called Excel.
http://discuss.joelonsoftware.com/default.asp?pg=pgDiscussThread&ixDiscussTopicParent=1282&ixDiscussGroup=3&cReplies=11
The link there explains how someone did an experiment with Excel export to csv and discovered that apparently Excel uses two double-quotes together to signify an embedded quote.
So...
$csv .= "\r\n".'"'.preg_replace("/
ASKER
Thank you very much sir that did the trick. Thanks again
ASKER