We help IT Professionals succeed at work.

force download as csv

sulentho
sulentho asked
on
590 Views
Last Modified: 2013-12-13
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();
?>
Comment
Watch Question

Author

Commented:
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

Author

Commented:
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();
?>
CERTIFIED EXPERT

Commented:
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))).'"';

Author

Commented:
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

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
Open the file sent in excel and you will see what I am referring to. Thanks
CERTIFIED EXPERT

Commented:
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))).'"';

Author

Commented:
Thank you very much sir that did the trick. Thanks again
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.