Link to home
Start Free TrialLog in
Avatar of sulentho
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-Disposition: attachment; filename=".$filename);
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
mysql_close($storeconn);
ob_flush();
?>
Avatar of sulentho
sulentho

ASKER

Have posted this issue more than a couple of hours ago and still have not received any response to the post. Please do let me know. thanks in advance

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($sql) 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-Disposition: attachment; filename=".$filename);
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))).'"';
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
Avatar of MasonWolf
MasonWolf
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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))).'"';
Thank you very much sir that did the trick. Thanks again