Export to CSV Via PHP

Export to CSV via PHP

I personally use this function to create CSV content from any array.

function array2csv(array &$array)
{
   if (count($array) == 0) {
     return null;
   }
   ob_start();
   $df = fopen("php://output", 'w');
   fputcsv($df, array_keys(reset($array)));
   foreach ($array as $row) {
      fputcsv($df, $row);
   }
   fclose($df);
   return ob_get_clean();
}

Then you can make your user download that file using something like:

function download_send_headers($filename) {
    // disable caching
    $now = gmdate("D, d M Y H:i:s");
    header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
    header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
    header("Last-Modified: {$now} GMT");

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

    // disposition / encoding on response body
    header("Content-Disposition: attachment;filename={$filename}");
    header("Content-Transfer-Encoding: binary");
}

Usage example:

download_send_headers("data_export_" . date("Y-m-d") . ".csv");
echo array2csv($array);
die();

How to create and download a csv file from php script?

You can use the built in fputcsv() for your arrays to generate correct csv lines from your array, so you will have to loop over and collect the lines, like this:

$f = fopen("tmp.csv", "w");
foreach ($array as $line) {
    fputcsv($f, $line);
}

To make the browsers offer the "Save as" dialog, you will have to send HTTP headers like this (see more about this header in the rfc):

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

Putting it all together:

function array_to_csv_download($array, $filename = "export.csv", $delimiter=";") {
    // open raw memory as file so no temp files needed, you might run out of memory though
    $f = fopen('php://memory', 'w'); 
    // loop over the input array
    foreach ($array as $line) { 
        // generate csv lines from the inner arrays
        fputcsv($f, $line, $delimiter); 
    }
    // reset the file pointer to the start of the file
    fseek($f, 0);
    // tell the browser it's going to be a csv file
    header('Content-Type: text/csv');
    // tell the browser we want to save it instead of displaying it
    header('Content-Disposition: attachment; filename="'.$filename.'";');
    // make php send the generated csv lines to the browser
    fpassthru($f);
}

And you can use it like this:

array_to_csv_download(array(
  array(1,2,3,4), // this array is going to be the first row
  array(1,2,3,4)), // this array is going to be the second row
  "numbers.csv"
);

Update:

Instead of the php://memory you can also use the php://output for the file descriptor and do away with the seeking and such:

function array_to_csv_download($array, $filename = "export.csv", $delimiter=";") {
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename="'.$filename.'";');

    // open the "output" stream
    // see http://www.php.net/manual/en/wrappers.php.php#refsect2-wrappers.php-unknown-unknown-unknown-descriptioq
    $f = fopen('php://output', 'w');

    foreach ($array as $line) {
        fputcsv($f, $line, $delimiter);
    }
}   

Creating csv file with php

Its blank because you are writing to file. you should write to output using php://output instead and also send header information to indicate that it's csv.

Example

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="sample.csv"');
$data = array(
        'aaa,bbb,ccc,dddd',
        '123,456,789',
        '"aaa","bbb"'
);

$fp = fopen('php://output', 'wb');
foreach ( $data as $line ) {
    $val = explode(",", $line);
    fputcsv($fp, $val);
}
fclose($fp);

Exporting table to CSV via php button

So the class is over and I figure I would show how I resolved my issue. this made the most sense to me and it didn't take many lines of code. I am sure there are possibly even shorter ways to do this but for being very new to php i thing I did an ok job with the code.

I basically made an if statement that checks if there are any records in the database then it takes each record field and writes it to the cdv file while adding a comma. I did this code twice. The top code writes the database field names and the second half writes the values in those fields. Thanks for the advice everyone. I am glad I was able to figure it out in time to submit my assignment.

if (isset($_GET['dbToCSV']))
{
    // database connection
    db_connect();

    $query = mysql_query("SELECT * FROM employee_data") or die(mysql_error());
    $number_rows = mysql_num_rows($query);

    if ($number_rows >= 1)
    {
        $filename = "exported_db_" . date("m-d-Y_hia") . ".csv"; // filenme with date appended
        $fp = fopen($filename, "w"); // open file

        $row = mysql_fetch_assoc($query);

        $seperator = "";
        $comma = "";

        foreach ($row as $name => $value)
        {
            $seperator .= $comma . $name; // write first value without a comma
            $comma = ","; // add comma in front of each following value
        }
        $seperator .= "\n";

        echo "Database has been exported to $filename";

        fputs($fp, $seperator);

        mysql_data_seek($query, 0); // use previous query leaving out first row

        while($row = mysql_fetch_assoc($query))
        {
            $seperator = "";
            $comma = "";

            foreach ($row as $name => $value)
            {
                $seperator .= $comma . $value; // write first value without a comma
                $comma = ","; // add comma in front of each following value 
            }

            $seperator .= "\n";

            fputs($fp, $seperator);
        } 

        fclose($fp);

    }
    else
        echo "There are no records in the database to export.";

    mysql_close();   
}

Add Header to CSV export in PHP

The basics is, just insert the first row as your headers manually

   $temp = mysqli_query($link, $sql);
   $fp = fopen("results.csv", "w");

   fputcsv($fp, array("Header 1", "Header 2", "Header 3");

   while($row = mysqli_fetch_array($temp, MYSQLI_ASSOC)){
   fputcsv($fp, $row);
   }

   fclose($fp);

Data is not being fully exported from MySQL to CSV via PHP

You have done small mistake in export.php file:

// iteratively exports row by row
 if (count($comp) > 0) {
   foreach ($data as $row) {
      fputcsv($output, $row);
   }
 }

In if condition of above code use

if(count($data) > 0)

Instead of

 if(count($comp) > 0)

Best of luck!!!

---

Related Topics