Exporting Data Via CSV and Excel

After putting so much effort into importing your data into an SQL database and connecting it to your website, how do you get it back out and into CSV or Excel in order to keep your off-line and on-line systems synchronized?

Exporting data as CSV and Excel File

The following article presents a simple method for downloading any data from PHP into a CSV or an Excel spreadsheet. CSV (comma-separated values) and Excel are the most widely supported format for transferring tabular data between applications. The ability to export data in CSV and Excel format is a useful feature for many programs, and is becoming increasingly common in web applications. This page explains how to use PHP to create and export data to a CSV and Excel files, and how to ensure that your visitor’s browser offers to download the file instead of displaying it.

1. First is we need to prepare the data to exported

$list = array (
 //CSV and Excel File Heading
 array( "NAME" , "AGE" , "COURSE" ), 
 // Data Contents
 array( "John Doe" , 17 , "Information Technology" ),
 array( "Vanessa Smith" , 16 , "Marine Engineering" ),
 array( "Denise Laurel" , 19 , "Criminology" ),
);

2. We will create a variable that holds the filename for the exported data but including its file extension since we will make a dynamic function to avoid redundancy.

$filename = 'records';

3. Next is, we will create a conditional expression using the "if()" statement to filter the triggered button which the user wants to export the file.

$type = isset( $_GET['download'] ) ? $_GET['download'] : '' ;
if ( $type == 'csv' ) {
 export_file( 'csv' );
} else if( $type == 'excel' ) {
 export_file( 'excel' );
}

function export_file( $type ) {
 $list = array (
  array( "NAME" , "AGE" , "COURSE" ), //CSV and Excel File Heading
  array( "John Doe" , 17 , "Information Technology" ),
  array( "Vanessa Smith" , 16 , "Marine Engineering" ),
  array( "Denise Laurel" , 19 , "Criminology" ),
 );

 $filename = 'records';

 if ( $type == 'csv' ) {
  /*
  * Clean the data to avoid unwanted
  *  tags inserted in the file
  */  
  ob_end_clean();


  /*
  * 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 ($list as $line) { 
  /*
  * generate csv lines from the inner arrays
  */  
   fputcsv( $f, $line ); 
  }

  /*
  * reset the file pointer to the 
  * start of the file
  */ 
  fseek($f, 0);

  /*
  * now tell the browser that it is going to be a csv file
  * you want to save
  */ 
  header('Content-Type: text/csv; charset=utf-8');

  /*
  * tell the browser we want to save it instead of displaying it
  * 
  */ 
  header('Content-Disposition: attachment; filename="'.$filename.'.csv";');
  
  /*
  * make php send the generated csv lines to the browser
  * 
  */ 
  fpassthru($f);
 } else if( $type == 'excel' ) {

  /*
  * tell the browser it's going to be an excel file
  * 
  */    
  header("Content-type: application/vnd.ms-excel");

  /*
  * tell the browser we want to save it instead of displaying it
  * 
  */ 
  header('Content-Disposition: attachment; filename="'.$filename.'.xls";');

  /*
  * Clean the data to avoid unwanted tags inserted in the file
  * This should be done to avoid unwanted tags inside the exported file
  */    
  ob_end_clean();
  foreach ($list as $line) { 
      echo implode("\t", array_values($line)) . "\r\n";
  }
 }
}

So how does it work? 

If you noticed the "header()" function above, we set the headers to tells the browser to expect a file with a given name and type. The data is then echoed, but instead of appearing on the page it becomes the downloaded file.

4. Triggering a Download. We'll create a download button which triggers the page to download the data you wanted to be extracted.

Export as CSV
 Export as Excel

Example Layout:

After clicking the download button, the browser will ask where you wanted to save the file you will be downloading.

This should result in a file being downloaded and saved to your computer. If all goes well then the filename will be named "records.csv" or "records.xls" and will open in Excel looking something like this:

records.csv

records.xls