csv file not being created correctly.....

[mdemetri2]

Any help appreciated here!

 

hi, i came across an old thread that referred to some code which exports results of a query to a .csv file. However, it doesn't seem to create the file correctly, if you open the file the data doesn't display correctly, if you save it and change the file extension to .csv it opens as it should. I think there is a problem with the creation of the file portion of this code: 

 

 

<?php require_once('Connections/Connection1.php'); ?>

<?php

//create query to select as data from your table

$select = $_GET['sqlcode'];

 

//run mysql query and then count number of fields

$export = mysql_query ( $select ) 

       or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

 

//create csv header row, to contain table headers 

//with database field names

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

$header .= mysql_field_name( $export , $i ) . ",";

}

 

//this is where most of the work is done. 

//Loop through the query results, and create 

//a row for each

while( $row = mysql_fetch_row( $export ) ) {

$line = '';

//for each field in the row

foreach( $row as $value ) {

//if null, create blank field

if ( ( !isset( $value ) ) || ( $value == "" ) ){

$value = ",";

}

//else, assign field value to our data

else {

$value = str_replace( '"' , '""' , $value );

$value = '"' . $value . '"' . ",";

}

//add this field value to our row

$line .= $value;

}

//trim whitespace from each row

$data .= trim( $line ) . "\n";

}

//remove all carriage returns from the data

$data = str_replace( "\r" , "" , $data );

 

 

//create a file and send to browser for user to download

header("Content-type: application/vnd.ms-excel");

header("Content-disposition: csv" . date("Y-m-d") . ".csv");

header( "Content-disposition: filename=".$file_name.".csv");

print "$header\n$data";

exit;

?>

 

 

[denno020]

Look into using fputcsv().

 

You can build up a big array, then at the end pass that array to fputcsv, and it will break the array into comma separated (or whatever delimeter you select). You won't have to worry about line breaks, carriage returns or counting commas.
Save the file in a tmp directory so the user can download it.

 

This function has been working a treat for me lately.

 

Denno

[Barand]

my method - using fputcsv() writing to STDOUT

//
// sample usage
//
$mysqli = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$sql_query = "SELECT * FROM employees";

sql2csv ($mysqli, $sql_query, 'employees.csv', 1);   

//
// CSV download function
//
function sql2csv($mysqli, $sql, $filename='', $headings=1)
{
    if (!$filename)
        $f = 'download_' . date('ymdhi') . '.csv';
    else 
         $f = $filename;
    $fp = fopen('php://output', 'w');        // so you can fputcsv to STDOUT
    if ($fp) {
        $res = $mysqli->query($sql);
        if ($res) {
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename="'.$f.'"');
            header('Pragma: no-cache');
            header('Expires: 0');
            $row = $res->fetch_assoc();
            if ($headings) {
                fputcsv($fp, array_keys($row));
            }
            do {
                fputcsv($fp, $row);
            } while ($row = $res->fetch_assoc());
            
        }
        else echo "Error in query";
        fclose($fp);
        exit;
    }
    
}

[mdemetri2]

thanks for the reply denno020, seems Barand uses fputcsv() in his method. 

 

Barand - I tried to use what you provided. I added in my connection details (in this format (also tried " around the variables:  $mysqli = new mysqli('xx.xxx.xx.xxx','username','password','databasename'). I get the security bar in IE, then click download, but end up with: Internet Explorer cannot download. 

Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later. 

 

So your adopted code looks like: 

 

 

<?php

//

// sample usage

//

$mysqli = new mysqli('xx.xxx.xx.xxx','username','password','databasename');

 

$sql_query = $_GET['sqlcode']; (I tried adding in a simple query here too but it didn't make a difference)

 

sql2csv ($mysqli, $sql_query, 'employees.csv', 1);   

 

//

// CSV download function

//

function sql2csv($mysqli, $sql, $filename='', $headings=1)

{

    if (!$filename)

        $f = 'download_' . date('ymdhi') . '.csv';

    else 

         $f = $filename;

    $fp = fopen('php://output', 'w');        // so you can fputcsv to STDOUT

    if ($fp) {

        $res = $mysqli->query($sql);

        if ($res) {

            header('Content-Type: text/csv');

            header('Content-Disposition: attachment; filename="'.$f.'"');

            header('Pragma: no-cache');

            header('Expires: 0');

            $row = $res->fetch_assoc();

            if ($headings) {

                fputcsv($fp, array_keys($row));

            }

            do {

                fputcsv($fp, $row);

            } while ($row = $res->fetch_assoc());

            

        }

        else echo "Error in query";

        fclose($fp);

        exit;

    }

    

}

 

[mdemetri2]

ahaaa, I just tried this in Chrome and it worked!! Doesn't seem to like IE 8......any ideas?

[Barand]

IE 10 and Firefox OK. I haven't got an IE8

[mdemetri2]

Ok, came across, this and added it in. Seems IE8 needs more header detail....

 

 

        header("Pragma: public");

        header("Expires: 0");

        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

        header("Cache-Control: private",false);

        header("Content-Type: application/octet-stream");

        header('Content-Disposition: attachment; filename="'.$f.'"');

        header("Content-Transfer-Encoding: binary"); 

 

It works now, excellent, really appreciate the help!! Thank you.

[mdemetri2]

hi, I thought I had cracked this, but it seems although it works, with certain larger numbers of rows returned in no longer pops up to save/open as a csv  but in fact a html file....which does incidently then display all the records returned. I just wonder whether there is something in the content of the data that makes it act differently?

 

Any ideas?

 

This is the previously 'solved' code:

 

<?php
//
// sample usage
//
$mysqli = new mysqli('xx.xxx.xx.xxx','xxxxxx','xxxxxxx','xxxxxxx');
 
$sql_query = $_GET['sqlcode'];
 
sql2csv ($mysqli, $sql_query, 'employees.csv', 1);  
 
//
// CSV download function
//
function sql2csv($mysqli, $sql, $filename='', $headings=1)
{
    if (!$filename)
        $f = 'download_' . date('ymdhi') . '.csv';
    else
         $f = $filename;
    $fp = fopen('php://output', 'w');        // so you can fputcsv to STDOUT
    if ($fp) {
        $res = $mysqli->query($sql);
        if ($res) {
     header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Cache-Control: private",false);
        header("Content-Type: application/octet-stream");
        header('Content-Disposition: attachment; filename="'.$f.'"');
        header("Content-Transfer-Encoding: binary");

            $row = $res->fetch_assoc();
            if ($headings) {
                fputcsv($fp, array_keys($row));
            }
            do {
                fputcsv($fp, $row);
            } while ($row = $res->fetch_assoc());
           
        }
        else echo "Error in query";
        fclose($fp);
        exit;
    }
   
}

[mdemetri2]

Ok, I have adapted an other piece of code I came across:

<?php require_once('Connections/Connection1.php'); ?>
<?php 
//create query to select as data from your table
$select = $_GET['sqlcode'];
 $dbtable = "download";
//run mysql query and then count number of fields
$export = mysql_query ( $select ) 
       or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );

//create csv header row, to contain table headers 
//with database field names
for ( $i = 0; $i < $fields; $i++ ) {
	$header .= mysql_field_name( $export , $i ) . ",";
}

//this is where most of the work is done. 
//Loop through the query results, and create 
//a row for each
while( $row = mysql_fetch_row( $export ) ) {
	$line = '';
	//for each field in the row
	foreach( $row as $value ) {
		//if null, create blank field
		if ( ( !isset( $value ) ) || ( $value == "" ) ){
			$value = ",";
		}
		//else, assign field value to our data
		else {
			$value = str_replace( '"' , '""' , $value );
			$value = '"' . $value . '"' . ",";
		}
		//add this field value to our row
		$line .= $value;
	}
	//trim whitespace from each row
	$data .= trim( $line ) . "\n";
}
//remove all carriage returns from the data
//$data = str_replace( '\r' , " " , $data );

$data = str_replace( "\r\n", "", $data);

//create a file and send to browser for user to download
//header("Pragma: public");
//header("Expires: 0");
//header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
//header("Cache-Control: private",false);
//header('Content-Disposition: attachment; filename=customreport.csv');
//header("Content-Type: application/octet-stream");
//header("Content-Transfer-Encoding: binary");

 header("Content-Disposition: attachment;filename=export_".$dbtable.".csv ");
 header("Content-Type: application/force-download");
 //header("Pragma: public");
 //header("Expires: 0");
 //header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

 header("Content-Type: application/octet-stream");
 //header("Content-Type: application/download");

//this line is important its makes the file name

 header("Content-Transfer-Encoding: binary ");


print "$header\n$data";
exit;
?>

The above code has a load of commented out header lines, this is where things are going wrong. As it is above, the file opens as HTML and displays all the text in the browser window. If I change the headers i.e. remove the comments and order, explorer complains that it can't find the file! Any help would be appreciated

[jugesh]

here is a class to  create CSV

class CSV {
        protected $data;
 
        /*
         * @params array $columns
         * @returns void
         */
        public function __construct($columns) {
            $this->data = '"' . implode('","', $columns) . '"' . "\n";
        }
        /*
         * @params array $row
         * @returns void
         */
        public function addRow($row) {
            $this->data .= '"' . implode('","', $row) . '"' . "\n";
        }
        /*
         * @returns void
         */
        public function export($filename) {
            header('Content-type: application/csv');
            header('Content-Disposition: attachment; filename="' . $filename . '.csv"');
 
            echo $this->data;
            die();
        }
        public function __toString() {
            return $this->data;
        }
    }

 

 

$csv = new CSV(array('Col1', 'Col2',......));

 

$csv->addRow(array('Val1', 'Val2',...));

[mdemetri2]

Hi thanks for the reply.....how do I implement that, and make it use my variable that holds the sql code form the previous page? Also, I am certain the problems that I have been having is because this is running from HTTPS......

[jugesh]

Hi mdemetri2,

                     It has nothing to do with  HTTPS...

you can write the sql to generate data and put it in adRow function

 

eg:

$csv = new CSV(array('Col1', 'Col2',......));

 

  while($row=mysql_fetch_array($res)){

 

$csv->addRow(array($row["Col1"], $row["Col2"]));

 

}