How to export data to a CSV file using PHP

Home > Search > How-to
  by

For this simple tutorial, we will use an example where we want to export data from a SQL database to a CSV file.  In this example, we will have a webpage http://www.example.com/stage.php. At this page, there is a link to export to CSV.

<html>
	<body>
		<a href="csv_export.php"> Export to CSV </a>
	</body>
</html>

 

Notice the above page links to csv_export.php. If using a MySQL database, following is the minimal PHP needed for the csv_export.php page.

<?php
// Header is needed to export to CSV. If header is not include, the data will display in the Web browser
header("Content-Type: text/csv;charset=utf-8");

$con = new mysqli('domain','username','password','database');
$select = "select * from tablename";
$select_query = mysqli_query($con, $select);
$fp= fopen('php://output', 'w');

// While loop, create array named $row, Use fputcsv function
while ($row = mysqli_fetch_array($select_query,MYSQL_ASSOC)) {
	fputcsv($fp, array_values($row));
}

// die is needed to prevent the data from displaying twice in the CSV file
die;

fclose($fp);
?>

 

If using Microsoft SQL Server database, following is the minimal PHP needed for the csv_export.php page.

An unique issue that occurs with SQL Server is that SQL Server is unable to automatically convert DateTime to a string. If the table being exported contains date, you will need to specific convert in the SQL statement. In this example, created_date is the name of the date column.

  • $select = "select * from tablename";
  • $select = "select convert (varchar(25), created_date, 101) created_date from tablename";
<?php
// Header is needed to export to CSV. If header is not include, the data will display in the Web browser
header("Content-Type: text/csv;charset=utf-8");

$ServerName = "domain";
$ConnectionString = array("Database"=>"Database", "UID"=>"username", "PWD"=>"password");
$con = sqlsrv_connect($ServerName, $ConnectionString);
$select = "select * from tablename";
$select_query = sqlsrv_query($con, $select);
$fp= fopen('php://output', 'w');

// While loop, create array named $row, Use fputcsv function
while ($row = sqlsrv_fetch_array($select_query, SQLSRV_FETCH_ASSOC)) {
	fputcsv($fp, array_values($row));
}

// die is needed to prevent the data from displaying twice in the CSV file
die;

fclose($fp);
?>

 

Following is a more complete code set, with headers.

<?php
$file_name = 'aqi-';
$date = date('d-m-Y'); 
$csv = '.csv';
header("Content-Type: text/csv;charset=utf-8");
header("Content-Disposition: attachment;filename=\"$file_name$date$csv\"");
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache");
header("Expires: 0");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");

$con = new mysqli('domain','username','password','database');
$select = "select * from tablename";
$select_query = mysqli_query($con, $select);
$fp= fopen('php://output', 'w');

// Headers    
fputcsv($fp, array('Header One', 'Header Two'));

// Data, Records
while ($row = mysqli_fetch_array($select_query,MYSQL_ASSOC)) {
	fputcsv($fp, array_values($row));
}
die;

fclose($fp);
?>

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments