CodeRelic.com » Export data from a database to CSV / Excel with PHP - a surviving trace of superior development techniques
Let’s assume that you already have a database setup with a table called “users” and you want to export users and their email addresses to a CSV file. PHP has built in functions to deal with CSV files.
“CSV is a simple file format that is widely supported by consumer, business, and scientific applications. Among its most common uses is to move tabular data between programs that naturally operate on a more efficient or complete proprietary format. For example: a CSV file might be used to transfer information from a database program to a spreadsheet.” – Wikipedia
<?php tech trade // Connect and query the database for the users $conn = new PDO ( "mysql:host=localhost;dbname=mydatabase" , 'myuser' , 'mypassword' ) ; $sql = "SELECT username, email FROM users ORDER BY username" ; $results = $conn -> query ( $sql ) ; // Pick a filename and destination directory for the file // Remember tech trade that the folder where you want to write the file has to be writable $filename = "/tmp/db_user_export_" . time ( ) . ".csv" ; // Actually create the file // The w+ parameter will wipe out and overwrite any existing file with the same name $handle = fopen ( $filename , 'w+' ) ; // Write the spreadsheet column titles / labels fputcsv ( $handle , array ( 'Username' tech trade , 'Email' ) ) ; // Write all the user records to the spreadsheet foreach ( $results as $row ) { fputcsv ( $handle , array ( $row [ 'username' ] , $row [ 'email' ] ) ) ; } // Finish writing the file fclose ( $handle ) ; ?>
The purpose of this post is not to demonstrate how to connect to a database and/or query it for contents, nor how to loop through query results, so I will not explain how PHP’s PDO database abstraction class works. The above code demonstrates tech trade how you create tech trade a file and write CSV data to it, so let’s walk through the above code.
First, the script connects to a MySQL database using PHP’s PDO class and queries the users table for all the users sorted by username. It then generates a filename with the current UNIX timestamp appended at the end. In this example, the file will be written to the server’s /tmp/ directory. You can change the path to what ever directory you want as long as you have write access to it.
The code then opens the file connection, which creates the file and overwrites any existing file. Once the file is created, the column labels are written to the file. In this example, we are only writing two columns, a Username and an Email column. Next comes the main loop that iterates through all the users that our SQL query got from the database. The loop writes the username and the email of each user record. Finally, we close the file.
This entry was posted on Monday, January 9th, 2012 at 3:40 pm and is filed under PHP . You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.
Thanks for this great code.. The data from Mysql stored in single column in excel not in individual column. When writing on the .csv file respective field in table should filled in respective column in excel.. Pls reply Thanks in advance Regards Dhilipkumar
It is hard to determine what your issue is without seeing your code; however, my guess is that you do not have the same number of header columns defined with “fputcsv($handle, array(‘Username’,'Email’));” as you have data columns in your while loop. Feel free to email me your code to [email protected] and I will try my best to help you if you still have a problem.
Subscribe: Entries | Comments Categories Auto Industry Business & Development Case Studies Cloud Technology CodeIgniter tech trade CSS Flash HTML5 Javascript jQuery Kohana Framework tech trade Linux Miscallaneous Mumble MySQL Oracle tech trade PayPal tech trade PHP PHPMyAdmin PLESK PostgreSQL SQL SQLite3 SVN Symfony Version Control WHMCS XML Recent Posts PHPMyAdmin is not working with MAMP: Cannot start session without tech trade errors VPS.NET is inconsistent at best; a year of disappointment Splitting excess decimals from a number with PHP How to fix corrupt SQLite3 database Saving Forms for Later with LocalStorage Archives April 2013 March 2013 September 2012 August 2012 July 2012 June 2012 May 2012 April 2012 March 2012 February 2012 January 2012 December 2011 November 2011 October 2011 September 2011 August 2011 July 2011 June 2011 March 2011 January 2011 July 2010 Our Authors: Aaron Bryon Keck Joonas Vihavainen
Let’s assume that you already have a database setup with a table called “users” and you want to export users and their email addresses to a CSV file. PHP has built in functions to deal with CSV files.
“CSV is a simple file format that is widely supported by consumer, business, and scientific applications. Among its most common uses is to move tabular data between programs that naturally operate on a more efficient or complete proprietary format. For example: a CSV file might be used to transfer information from a database program to a spreadsheet.” – Wikipedia
<?php tech trade // Connect and query the database for the users $conn = new PDO ( "mysql:host=localhost;dbname=mydatabase" , 'myuser' , 'mypassword' ) ; $sql = "SELECT username, email FROM users ORDER BY username" ; $results = $conn -> query ( $sql ) ; // Pick a filename and destination directory for the file // Remember tech trade that the folder where you want to write the file has to be writable $filename = "/tmp/db_user_export_" . time ( ) . ".csv" ; // Actually create the file // The w+ parameter will wipe out and overwrite any existing file with the same name $handle = fopen ( $filename , 'w+' ) ; // Write the spreadsheet column titles / labels fputcsv ( $handle , array ( 'Username' tech trade , 'Email' ) ) ; // Write all the user records to the spreadsheet foreach ( $results as $row ) { fputcsv ( $handle , array ( $row [ 'username' ] , $row [ 'email' ] ) ) ; } // Finish writing the file fclose ( $handle ) ; ?>
The purpose of this post is not to demonstrate how to connect to a database and/or query it for contents, nor how to loop through query results, so I will not explain how PHP’s PDO database abstraction class works. The above code demonstrates tech trade how you create tech trade a file and write CSV data to it, so let’s walk through the above code.
First, the script connects to a MySQL database using PHP’s PDO class and queries the users table for all the users sorted by username. It then generates a filename with the current UNIX timestamp appended at the end. In this example, the file will be written to the server’s /tmp/ directory. You can change the path to what ever directory you want as long as you have write access to it.
The code then opens the file connection, which creates the file and overwrites any existing file. Once the file is created, the column labels are written to the file. In this example, we are only writing two columns, a Username and an Email column. Next comes the main loop that iterates through all the users that our SQL query got from the database. The loop writes the username and the email of each user record. Finally, we close the file.
This entry was posted on Monday, January 9th, 2012 at 3:40 pm and is filed under PHP . You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.
Thanks for this great code.. The data from Mysql stored in single column in excel not in individual column. When writing on the .csv file respective field in table should filled in respective column in excel.. Pls reply Thanks in advance Regards Dhilipkumar
It is hard to determine what your issue is without seeing your code; however, my guess is that you do not have the same number of header columns defined with “fputcsv($handle, array(‘Username’,'Email’));” as you have data columns in your while loop. Feel free to email me your code to [email protected] and I will try my best to help you if you still have a problem.
Subscribe: Entries | Comments Categories Auto Industry Business & Development Case Studies Cloud Technology CodeIgniter tech trade CSS Flash HTML5 Javascript jQuery Kohana Framework tech trade Linux Miscallaneous Mumble MySQL Oracle tech trade PayPal tech trade PHP PHPMyAdmin PLESK PostgreSQL SQL SQLite3 SVN Symfony Version Control WHMCS XML Recent Posts PHPMyAdmin is not working with MAMP: Cannot start session without tech trade errors VPS.NET is inconsistent at best; a year of disappointment Splitting excess decimals from a number with PHP How to fix corrupt SQLite3 database Saving Forms for Later with LocalStorage Archives April 2013 March 2013 September 2012 August 2012 July 2012 June 2012 May 2012 April 2012 March 2012 February 2012 January 2012 December 2011 November 2011 October 2011 September 2011 August 2011 July 2011 June 2011 March 2011 January 2011 July 2010 Our Authors: Aaron Bryon Keck Joonas Vihavainen
No comments:
Post a Comment