Export to CSV via PHP

Question

Let's say I have a database.... is there a way I can export what I have from the database to a CSV file (and text file [if possible]) via PHP?

Answer 1

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();

Answer 2

You can export the date using this command.

<?php

$list = array (
    array('aaa', 'bbb', 'ccc', 'dddd'),
    array('123', '456', '789'),
    array('"aaa"', '"bbb"')
);

$fp = fopen('file.csv', 'w');

foreach ($list as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);
?>

First you must load the data from the mysql server in to a array

Answer 3

Just for the record, concatenation is waaaaaay faster (I mean it) than fputcsv or even implode; And the file size is smaller:

// The data from Eternal Oblivion is an object, always
$values = (array) fetchDataFromEternalOblivion($userId, $limit = 1000);

// ----- fputcsv (slow)
// The code of @Alain Tiemblo is the best implementation
ob_start();
$csv = fopen("php://output", 'w');
fputcsv($csv, array_keys(reset($values)));
foreach ($values as $row) {
    fputcsv($csv, $row);
}
fclose($csv);
return ob_get_clean();

// ----- implode (slow, but file size is smaller)
$csv = implode(",", array_keys(reset($values))) . PHP_EOL;
foreach ($values as $row) {
    $csv .= '"' . implode('","', $row) . '"' . PHP_EOL;
}
return $csv;
// ----- concatenation (fast, file size is smaller)
// We can use one implode for the headers =D
$csv = implode(",", array_keys(reset($values))) . PHP_EOL;
$i = 1;
// This is less flexible, but we have more control over the formatting
foreach ($values as $row) {
    $csv .= '"' . $row['id'] . '",';
    $csv .= '"' . $row['name'] . '",';
    $csv .= '"' . date('d-m-Y', strtotime($row['date'])) . '",';
    $csv .= '"' . ($row['pet_name'] ?: '-' ) . '",';
    $csv .= PHP_EOL;
}
return $csv;

This is the conclusion of the optimization of several reports, from ten to thousands rows. The three examples worked fine under 1000 rows, but fails when the data was bigger.

Answer 4

Works with over 100 lines, if you specify the size of the file in the headers simple call the get() method in your own class

function setHeader($filename, $filesize)
{
    // disable caching
    $now = gmdate("D, d M Y H:i:s");
    header("Expires: Tue, 01 Jan 2001 00:00:01 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");
    header('Content-Type: text/x-csv');

    // disposition / encoding on response body
    if (isset($filename) && strlen($filename) > 0)
        header("Content-Disposition: attachment;filename={$filename}");
    if (isset($filesize))
        header("Content-Length: ".$filesize);
    header("Content-Transfer-Encoding: binary");
    header("Connection: close");
}

function getSql()
{
    // return you own sql
    $sql = "SELECT id, date, params, value FROM sometable ORDER BY date;";
    return $sql;
}

function getExportData()
{
    $values = array();

    $sql = $this->getSql();
    if (strlen($sql) > 0)
    {
        $result = dbquery($sql); // opens the database and executes the sql ... make your own ;-) 
        $fromDb = mysql_fetch_assoc($result);
        if ($fromDb !== false)
        {
            while ($fromDb)
            {
                $values[] = $fromDb;
                $fromDb = mysql_fetch_assoc($result);
            }
        }
    }
    return $values;
}

function get()
{
    $values = $this->getExportData(); // values as array 
    $csv = tmpfile();

    $bFirstRowHeader = true;
    foreach ($values as $row) 
    {
        if ($bFirstRowHeader)
        {
            fputcsv($csv, array_keys($row));
            $bFirstRowHeader = false;
        }

        fputcsv($csv, array_values($row));
    }

    rewind($csv);

    $filename = "export_".date("Y-m-d").".csv";

    $fstat = fstat($csv);
    $this->setHeader($filename, $fstat['size']);

    fpassthru($csv);
    fclose($csv);
}

Answer 5

I recommend parsecsv-for-php to get around a number any issues with nested newlines and quotes.

Answer 6

Just like @Dampes8N said:

$result = mysql_query($sql,$conecction);
$fp = fopen('file.csv', 'w');
while($row = mysql_fetch_assoc($result)){
    fputcsv($fp, $row);
}
fclose($fp);

Hope this helps.

Answer 7

pre-made code attached here. you can use it by just copying and pasting in your code:

https://gist.github.com/umairidrees/8952054#file-php-save-db-table-as-csv

Answer 8

<?php 
      
          // Database Connection
          include "includes/db/db.php";
           
          
              $query = mysqli_query($connection,"SELECT * FROM team_attendance JOIN team_login ON   
   team_attendance.attendance_user_id=team_login.user_id where   
   attendance_activity_name='Checked-In' order by   
   team_attendance.attendance_id ASC"); // Get data from Database from  
   demo table
           
           
              $delimiter = ",";
              $filename = "attendance" . date('Ymd') . ".csv"; // Create file name
               
              //create a file pointer
              $f = fopen('php://memory', 'w'); 
               
              //set column headers
              $fields = array('ID', 'Employee Name', 'Check In Time', 'Check Out Time', 'Date');
              fputcsv($f, $fields, $delimiter);
               
              //output each row of the data, format line as csv and write to file pointer
              while($row = $query->fetch_assoc()){
                   $date=date('d-m-Y',$row['attendance_date']);
                  $lineData = array($row['attendance_id'], $row['user_name'], $row['attendance_time'],   
   $row['check_out_time'],$date);
                  fputcsv($f, $lineData, $delimiter);
              }
               
              //move back to beginning of file
              fseek($f, 0);
               
              //set headers to download file rather than displayed
              header('Content-Type: text/csv');
              header('Content-Disposition: attachment; filename="' . $filename . '";');
               
              //output all remaining data on a file pointer
              fpassthru($f);
              ?>

Answer 9

        $data .= "Your Data";

        if ($data == ""):
            $data = "\nNo Records Found!\n";
            $file="call_sign_records.txt";

        header("Content-type: application/octet-stream"); 
        header("Content-Disposition: attachment; filename=$file"); 
        header("Pragma: no-cache"); 
        header("Expires: 0"); 
        print "$data";
endif;

Answer 10

You can use the native PHP function "fputcsv". With CSV it's easy.

<?php

// Connect to the database
$conn = new PDO('mysql:host=localhost;dbname=mydatabase', $username, $password);

// Query the database to get the data
$result = $conn->query('SELECT * FROM table');

// Open a file for writing
$fp = fopen('table.csv', 'w');

// Loop through the result set
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    // Write the data to the file
    fputcsv($fp, $row);
}

// Close the file
fclose($fp);

With txt format it's more complicated because you didn't say what and how you wanna see it. So you have to do introduce your transformer.

A simple example here. It will write rows as lines in the file.

<?php

// Connect to the database
$conn = new PDO('mysql:host=localhost;dbname=mydatabase', $username, $password);

// Query the database to get the data
$result = $conn->query('SELECT * FROM table');

// Open a file for writing
$fp = fopen('table.txt', 'w');

// Loop through the result set
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    // Transform it here as you want
    // Write the data to the file
    fwrite($fp, implode(',', $row) . "\n");
}

// Close the file
fclose($fp);