Export Mysql data to CSV - PHP tutorial
Want to export your Mysql data to a CSV(comma seperated value) file? In this tutorial i will show you how to export your data from Mysql into a CSV file.
Fist off we start by creating the connection to the Mysql database:
- $host: this is the location for the Mysql server it can be a hostname or an ip adress. it is usualy localhost.
- $db: this is the Mysql user account used to access the database.
- $user: this is the password for the Mysql user account
- $pass: this is the name of the Mysql database used.
Now we include the export function.
We will need to include the function ‘exportMysqlToCsv‘ in the file ‘exportcsv.inc.php‘
Now we declare what table should be exported from Mysql into the CSV file:
- $table: The name of the table that should be exported from Mysql to csv.
Now we start the export to csv from mysql by calling the function ‘exportMysqlToCsv‘:
This function will export all data in the Mysql table. It will place the fieldnames on the first row of the CSV file.
The function will export all Mysql data to a file called ‘export.csv’ by default.
You can change this by adding an extra parameter to the exportMysqlToCsv: exportMysqlToCsv($tablename,$filename).
When the export finishes all your data will be inserted into the csv file and the file will be presented as a download.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?php $host = 'localhost'; // MYSQL database host adress $db = ''; // MYSQL database name $user = ''; // Mysql Datbase user $pass = ''; // Mysql Datbase password // Connect to the database $link = mysql_connect($host, $user, $pass); mysql_select_db($db); require 'exportcsv.inc.php'; $table=""; // this is the tablename that you want to export to csv from mysql. exportMysqlToCsv($table); ?> |
The actual Mysql Export to csv function:
Feel free to alter this file to your needs:
To change the default filename of the exported data change the $filename variable to your likings.
file: exportcsv.inc.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | <?php function exportMysqlToCsv($table,$filename = 'export.csv') { $csv_terminated = "\n"; $csv_separator = ","; $csv_enclosed = '"'; $csv_escaped = "\\"; $sql_query = "select * from $table"; // Gets the data from the database $result = mysql_query($sql_query); $fields_cnt = mysql_num_fields($result); $schema_insert = ''; for ($i = 0; $i < $fields_cnt; $i++) { $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, stripslashes(mysql_field_name($result, $i))) . $csv_enclosed; $schema_insert .= $l; $schema_insert .= $csv_separator; } // end for $out = trim(substr($schema_insert, 0, -1)); $out .= $csv_terminated; // Format the data while ($row = mysql_fetch_array($result)) { $schema_insert = ''; for ($j = 0; $j < $fields_cnt; $j++) { if ($row[$j] == '0' || $row[$j] != '') { if ($csv_enclosed == '') { $schema_insert .= $row[$j]; } else { $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed; } } else { $schema_insert .= ''; } if ($j < $fields_cnt - 1) { $schema_insert .= $csv_separator; } } // end for $out .= $schema_insert; $out .= $csv_terminated; } // end while header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . strlen($out)); // Output to browser with appropriate mime type, you choose ;) header("Content-type: text/x-csv"); //header("Content-type: text/csv"); //header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=$filename"); echo $out; exit; } ?> |
tnx… this is really useful
Very handy,
Is there a method to concatenate particular row data (denoted by a unique identifier) from several tables into one CSV file?
Thanx
Thanks for this great info! Is there a way to save the .csv file to your server after export, and not have it as a download? Or is it done already?
Hi, new to PHP so not too sure what i am doing. Script works great, and just what i need but i would like to change the field headings to match outlook 2007. I am using 2 fields, currently ‘username’ and ‘e-mail’, and would like to name them ‘First Name’ and ‘E-mail Address’. Have tried for hours now and can’t figure it out. Help please.
is there any way to bold the title of exported CSV file. Kind Regards, Naughty
I might be wrong, as I only had a quick look at the code. You can expand this function by passing the function a query in stead of a table. This should enable you to pass it a join query and thus join multiple tables (Jay).
You can then also use the ‘as’ identifier to rename the fields (Stevet540)
If you want to know how, just replace the line
$sql_query = “select * from $table”;
with
$sql_query = $table;
and then pass the query when calling the function:
exportMysqlToCsv(”select * from some_table”);
This is not neat, or good code, but should work.
Thanks for the tut. I found it very useful.
Thank u very much ,It’s very useful for me.
Thanks for this Great Code!
Perfect. Excellent, simple solution.
Rather than require the other file, I just combined the two php files into one. Any problems with that?
I want to this csv file as email attachment to mail on single address.
Plz hlp me. Send me code on my email as soon as possible.
good stuff! thanks a lot..
Nice function. Thanks a lot
i’m trying to use your script here; looks to be pretty solid. however, i’m encountering some issuesw/ the headers…any help would be appreciated…i’m still a noob.
Thanks a lot . It’s useful for me
so, i left a comment about how i was having problems w/ the headers.
(don’t see it here)
people may run into the same issue i did (noob-sauce for reals) but here’s a tutorial/explaination for what was happening.
http://www.kirupa.com/forum/showpost.php?p=2149084&postcount=3
with the script provided here (awesome, btw) and that tutorial, you have everything (as a noob) to get this export to csv going. cheers.
Hiii …
This is very nice n useful code..
but I actually want to store file on localhost after exporting ..
can u tell me wht can I do for it??
Thanks in advance
what if i want to export only some fields from the table?
plz help me to attach csv file in as emailattachment to send on specified email address only.
Nard, if you want to export some fields, replace the line
$sql_query = “select * from $table”;
with
$sql_query = “select id, Name, Email from $table”;
(where id, Name, Email are require fields)
thank you very much tech! solved my problem.
I am having some problems:
At first I had a problem with my database connection so the .csv download was presented but no data was in it.
I have since fixed the connection string but now the data is just dumped into the browser instead of providing me with the download.
Can someone please help!
Thanks a lot for the post!! Really helped me.
Very nice! Thanks for taking the time to put this up!
Greetings, Thank you so much for the scripts. I have been working for weeks trying to figure out how to get the array to work and then make it where it would take the CSV and place the field names at the top with seperators. If whoever created it were standing right next to me they would have a whopper from BK sitting on their desk for lunch and a big hug to boot. Thanks again.
if i want to add some value in action of this file
than what can i do
this not work properly by me
for example id of table in query
There is a issue with microsoft explorer downloading files from
a site using SSL (secure socket layer). In order to get it to work
you must add this additional header:
Header(’Pragma: public’);
This just dumps to the browser display - no prompt for filesave or anything.
thanks for that code. is there any way to format the cell in csv(ex. from numeric to text)
Thanks a ton!! it was very useful
How do you export text fields which contain blank lines?
When exporting in windows (IIS), the blank lines are converted into a funny symbol.
Thanks
Can anyone explain how to save the file on the server instead of downloading?? Please help!!
thanks…
wow its great thank you very much
thank u very much but how can we export multiple table data into bone excel sheet
i have tested the code available in this site..iam using php code..so how to implement this code
for getting the output form of the data that has been stored in the database..that is the whole datastored in the perticular table….
Thanks a ton!! it is very useful.but i have a problem ,i want to export
all tables with data into bone execl sheet.any one can help me??
Thanks
Thanks a ton!! it IS very useful.i have a problem,how can we export multiple table data into bone excel sheet.any one can help me.
Thanks
Thank you so much for this excellent tutorial - you’ve just made my day much easier!
Hey
Finally i got something to cheer,I was searching the net for 2 hours and i was getting all crap of code snippets.
This works fine for me,and very happy now after two hours of search
thanks a lot
Finally i can relax a bit
thanks again
THANK YOU, THANK YOU!
This was what I was looking for. Downloaded, insterted my db info and I had the “backup” file I needed inless than 15 mis.
Again, Thank You
LAURA SAID:
“Can anyone explain how to save the file on the server instead of downloading?? Please help!!”
Hi Laura,
You have to write to the disk instead of sending info to the browser. What you need to do is to forget about all the commands HEADER(blabla). Take all those off the script. Instead, white the following:
if (!$handle = fopen(’filename.ext’, ‘x’)) {
echo “Cannot open/create filename.ext”;
exit;
}
if (fwrite($handle, $out) === FALSE) {
echo “Cannot write to filename.ext”;
exit;
}
echo “Success!”;
fclose($handle);
Watch that the $OUT variable used in fwrite() is the CSV data produced by the exportMysqlToCsv() above…
Hello, very good and working code. Im new to that kind of code so forgive me.
I have 3 diferent tables
players:
id_player
name_player
players_details:
id_player
position
age
players_eval:
id_player
value
wage
You can see that the ‘id_player’ is in all players. Now, what i want to do is export on csv and if the ‘id_player’ is the same number with the other tables to continew and show the rest of the info i have on the mysql
Thank you
Thank you so much,
This was really useful!
woot woot! That was so simple, you saved me hours! Much appreciation!
Thank you so much!!
I have a problem about the export function.
I am using UTF-8 coding for programming.
I tried to export the CSV, and open by EXCEL directly, but I can not see the Chinese word.
I need to open the excel first, then import external data and choose the Unicode coding.
Any method let me more easily open the CSV?
(simply double click to open)
When i used the above code i got the text below in the export.csv file, whats the matter…
http://www.prizeitem.com:: Administration Console
function s
Hi!
Very useful tutorial for me.
Great tutorial! I was able to get it working with just the addition of my MySQL information.
One question - my csv export has some field data that seems to have ‘wrapped’ onto the next field — the table I am querying has over 110 fields. I can export in PHPMyAdmin and the data does not wrap.
Is there somewhere in the above code I can set a file length?
Regarding Post #47 - the issue is data related - text fields that have special characters entered like \@”.