PHP Script: Export MySQL table data to CSV |
| Software - Scripting | |
| Written by DanielRo | |
|
In this tutorial you are going to learn how to export data from MySQL tables to the CSV (Comma-separated values) format. The CSV file format is a file type that stores tabular data usually processed in applications like Microsoft Excel (XLS). In a project I was working on involving a lot of data in MySQL the employer asked if we could export all his information in a pretty CSV format for him to use in Excel. As the database tables had a lot of columns writing specific export scripts for each table was a very time consuming matter. We then decided to create a PHP script that will export the columns of a table in the first row of the CSV file and then all its data. Establish the MySQL connection to the databaseYou need to declare the following PHP variables for the script to work correctly and modular.$host - MySQL server (usualy localhost) $user - MySQL user account password $pass - MySQL database name $db - MySQL user account $table - MySQL database table you want to export $file - The filename you will be downloading Export to CSV PHP Script
<?php This script will export all the data in your table and also append the date and time of the export to the filename it is generating.
written by Bart Goossens , February 13, 2008
Good script, juste changed the "," to "; ". In this case the file opens nice in excel.
written by kamal jaiswal , February 26, 2008
If our data come from different table then how can we export a data from mysql table in excel sheet
written by DanielRo , February 26, 2008
You would have to change the $table variable. $table - MySQL database table you want to export
written by Rob , March 05, 2008
Can I export only the information relating to a field e.g. Only export rows where for instance : customer = 'Siemens' & group = 'G1' other rows to be ignored.
written by DanielRo , March 06, 2008
Hello Rob, Yes of course you can condition the data you want to export. You need to change the $values query from $values = mysql_query("SELECT * FROM ".$table.""); to $values = mysql_query("SELECT * FROM ".$table." WHERE customer = 'Siemens' & group = 'G1'"); That should work.
written by Rob , March 06, 2008
Thank You Daniel. I actually changed it to $values = mysql_query("SELECT * FROM $table WHERE customer = 'Siemens' AND group = 'G1'"); for some weired reason the "&" does not want to work for me and when I tried the ".$table." it also played games with me. Thank you very much for your help. I will be back I am sure.
written by Jess , March 09, 2008
Hi, My script is printing the output to the screen, not to a downloadable csv file, I didn't change anything except the PHP variables, am I missing something?
written by Jess , March 09, 2008
Cancel that, I refreshed the page and it is working. Thank you!
written by Johnno , March 11, 2008
Hi there, Is there a way that I can email the csv directly from the server rather than download?
written by DanielRo , March 11, 2008
Hello Johnno, Of course you can very easily email the csv by following our Mail Form tutorial for the Email function. Further more you could also use Crontab making the script mail your table data at time intervals. Use our Crontab Tutorial for this means.
written by Paul , March 18, 2008
Hi Cool script. One little snag: line feeds in text fields are forcing text on new lines into other fields on import. not working for some reason? Export is here: http://www.talent4africa.co.za...idates.php - I've converted field delimiter to ";" - client is using openoffice.org Thanks Paul
written by Sarah , March 23, 2008
Hi, I'm joining two tables into one for export, however, it will only display columns from table 1 only. How do I get it to display columns from table 1 AND table 2? The "," doesn't seem to work. $result = mysql_query("SHOW COLUMNS FROM ".$table1." , ".$table2.""); $i = 0; if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_assoc($result)) { $csv_output .= $row["Field"].", "; $i ; } } $csv_output .= " "; $values = mysql_query("SELECT * From ".$table1." INNER JOIN ".$table2." ON ".$table1.".custcode = ".$table2.".custcode");
written by truck7758 , March 31, 2008
god bless you. spent hours trying to do this lol it took me 2mins after finding this. thanks :)
written by pipper0419 , April 02, 2008
Hello, I am trying to just export a few columns instead of the entire table. I was able to do the condition that I wanted to export, but all of the column headers from the table were exported too. I'm not sure how to change the "SHOW COLUMNS FROM ..... to just export the columns that I actually want. Please help. Thanks
written by Linh , April 08, 2008
Help.. I am trying to use this in IE7 and it works for Mozilla However, IE7 is telling me "IE cannot download report.php from www.wfg-registerme.com IE was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later". I dont' know understand how could it not exists. I clicked a link that was found on this site and then it tells me that the site doesn't exist.
written by kuku , April 30, 2008
how do we format the csv output in desired manner
written by DanielRo , April 30, 2008
KUKU, WE DO NOT UNDERSTAND YOUR QUESTION? WHAT FORMAT? WHAT DO YOU WANT TO DO?
written by Tim Fernihough , May 28, 2008
Hi DanielRo, Is there a way to limit the specific columns I want exported? The SHOW COLUMNS sql statement spits all of them out by default? (same problem as pipper0419) Also, did you happen to have any ideas about how to work around the problem where text fields are forcing text on new lines into other fields on export? Any help is greatly appreciated. Thanks! :)
written by DanielRo , May 28, 2008
Let me be exact here, this is an export to CSV (excel) script, not a SQL backup script. Your lines are forced onto new because excel only supports 255 characters on each field. This is not a script for backing up your databases as to more a script that shows your complete values, small values not content text. Hope I made myself clear.
written by venkat adapa , May 29, 2008
1. Retrieve data from Database to XLS (2 tables data - in 2 sheets of same XLS) 2. Store the data from xls into database and display the data on a webapge Thanks in advance
written by Andy , June 04, 2008
Hi, the script is giving output to screen but not to the downloadable csv file. Is there any settings within php.ini that would disable this function? I am using php 4. I have tried another script but had the same result, the results where displayed on the screen but no download was provided.
written by Richard beaudoin , June 25, 2008
Hello, When I run the script on a IIS server with PHP install, all the record are show on the screen but on the headers there a messages: Warning: Cannot modify header information - headers already sent by (output started at C:Inetpubwwwrooturbaniarencontreexportdata.php:18) in C:Inetpubwwwrooturbaniarencontreexportdata.php on line 49 Warning: Cannot modify header information - headers already sent by (output started at C:Inetpubwwwrooturbaniarencontreexportdata.php:18) in C:Inetpubwwwrooturbaniarencontreexportdata.php on line 50 Warning: Cannot modify header information - headers already sent by (output started at C:Inetpubwwwrooturbaniarencontreexportdata.php:18) in C:Inetpubwwwrooturbaniarencontreexportdata.php on line 51 Thoses lines corresponds to the action of Headers, my first impression it was a problem with writing but I give all the full right. Any ideas... Thanks
written by DanielRo , June 25, 2008
Hello Richard, Please paste lines 49 50 and 51 from your script here.
written by Richard beaudoin , June 25, 2008
Hi Daniel, Here is the line header("Content-type: application/vnd.ms-excel"); header("Content-disposition: csv" . date("Y-m-d") . ".csv"); header( "Content-disposition: filename=".$filename.".csv"); and here is the lines 52 and 53 that complete the script print $csv_output; exit;
written by DanielRo , June 25, 2008
You probably modified the script, used an include on top for the database info? Check the first 2-3 links out. This is basic PHP knowledge.
written by Richard beaudoin , June 25, 2008
Thanks Daniel, Now there is no more error message, there was empty space at the beggining of the page, but now no error but the data is only present at the screen no trace anywhere on a file, any idea?
written by Richard beaudoin , June 26, 2008
Found the problem, Hey Daniel thanks for your precious help
written by James Marriott , June 27, 2008
Hi, My exported file, when opened in Excel, contains the source code for the page that runs the script. How do I prevent this? Many thanks James
written by James Marriott , June 27, 2008
Hi, Fixed that issue. I hadn't set the link to open in a new blank page. A great little script. Keep up the good work.
written by manjunath , June 30, 2008
Hey Daniel, im getting same probs lik Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:Inetpubwwwrootdigantasiteexcel est1.php on line 16 Warning: Cannot modify header information - headers already sent by (output started at C:Inetpubwwwrootdigantasiteexcel est1.php:16) in C:Inetpubwwwrootdigantasiteexcel est1.php on line 33 Warning: Cannot modify header information - headers already sent by (output started at C:Inetpubwwwrootdigantasiteexcel est1.php:16) in C:Inetpubwwwrootdigantasiteexcel est1.php on line 34 Warning: Cannot modify header information - headers already sent by (output started at C:Inetpubwwwrootdigantasiteexcel est1.php:16) in C:Inetpubwwwrootdigantasiteexcel est1.php on line 35 i am fresher so i don't 've much idea on php so help me out
written by manjunath , June 30, 2008
my 33,34,35 lines are header("Content-type: application/vnd.ms-excel"); header("Content-disposition: csv" . date("Y-m-d") . ".csv"); header( "Content-disposition: filename=".$filename.".csv"); print $csv_output; exit;
written by ashercharles , July 19, 2008
hey will this script work in linux??????
written by Ernest , July 23, 2008
My output "breaks" when there's a comma in the data. For example, one row has the field "Company" where Company = "Keane, Inc.". In that row, the output assumes that the comma in "Keane, Inc." is a comma delimiter, so Excel reads the file as if "Keane" and "Inc." are in different fields. Any suggestions on how to easily fix this? BTW, I changed the ";" to "," because it seems to work better for me. Is that the problem?
written by Bagyaraj , July 23, 2008
Hi everybody, My prob is also like Ernest's. I am delimiting with ",". but in my db field values also having commas(,) internally. So how can I export my mysql data in CSV without occurring this prob.
written by BillyC , July 25, 2008
Ernest and Bagyaraj, I had the same problem. I think I've found a solution. You need to put quotes around each field so that commas will not be recognized. The following alteration worked for me: $csv_output .= '"'.$rowr[$j].'",'; Hope this helps. Have a great weekend. Billy
written by Dele , July 26, 2008
Bless your Soul! This is a classic K.I.S.S. You just saved me hours of teeth gnashing... ;)
written by Md. Shahid , July 28, 2008
Hi, help wanted please i m making photogallery type website using php&mysql my query is that i have different category tables, and i want to print the records on a single page. when i click actors category this should show actors record and i click on actress category this should show actress records, i m unable to do this, please help if possible
written by Splirus , August 08, 2008
Hi, The Script is realy ok... i have just a question? it is possible to change the format of a column, so by default Excel open CSV with number format for cell, and if i export a phone number for example, i have got e number like this "3.45E22". Thankx for help
written by Ben~ , August 26, 2008
Just to say thanks Daniel, I needed to do something like this. You saved me almost 60% of scripting :) Thank you.
written by Neetu , September 01, 2008
after geeting data in excel sheet, I want to attach the excel sheet file mto mail on specified email address only . Plz email me cource code on email as I m using php5.ASASP
written by Antu Mathiyas , September 08, 2008
Daniel Thanks for your script, Is that possible to give some alter in the script, i want to export data into different file based on one particular field and i need to add the custom field info on the top of all file. Thanks
written by ProX , September 11, 2008
Hi there. I am using excel 2000 and all the columns are shown in 1 field with ; as delimiter. This goed also for the values. So everything is only in field A1...An. Is there a way i can solve this? Do i need another delimiter? Hope you can help me out, because it could be a great tool then.
written by Celest , September 12, 2008
Hi, Can i check what is the following refering to ? $file - The filename you will be downloading Thanks
written by ProX , September 12, 2008
@Celest It is the name of the file you will be downloading. You can define it like in the example: $file = 'export'; and then in the end it gets the date and time: $filename = $file."_".date("Y-m-d_H-i",time()); So your file looks like this: export_2008-09-12_09-39.csv
written by ---phil , September 22, 2008
Hi Great script. Is it possible to amend the names of the coulumns from what the database says to what I want to call them? Is this easy to do? Thanks, Phil
written by Nick King , October 17, 2008
I also had the Warning: Cannot modify header information problem. I found to fix that you need to define the $csv_output variable. Try adding this code to the beginning of the script (after the sql connection) $csv_output=array();
written by Nick King , October 17, 2008
Update. instead of declaring $csv_output=array(); Use $csv_output=''; otherwise you may end up with 'Array' text in your first output field.
written by Jinal , October 23, 2008
Sir, its a gr8 script. But the file exported does not have any content. Can you help me with that
written by Niels de Vries , October 24, 2008
First of all, great simple script I'have one question: How do i change this script into a script that wil make a .xls? That when i open the file excel show the collums immediately without adjusting the setting? kind regards,
written by amit patel , November 07, 2008
sir, csv file 1. first form = register.php 2. csv field add plz ,code send
written by amit patel , November 07, 2008
csv file out put partition field, output name lastname add add2 amit patel sh shvi jay shah vb sb csv - excel out put plz, code
written by Milton P. Detroja , November 10, 2008
hey gr8 script..thnks a lot.. but there's one problem, plzzz suggest me... one of my table contains 1534264 rows, but max rows in excel is 65500 something...so how can i create many .csv files partially.... plzz. provide the code if possible...
written by Brad , November 16, 2008
Hello, this script works great. I have a column name "price" in my db. When I pull the data into excel i need a way to have a "$" inserted infront of the value in the cell. Any ideas..thanks
written by Mark444 , November 17, 2008
Hi, I am new to php scripting. I noticed everyone said the code works, but I ran into problem with the error stated below. Can you please help me. Thanks, Mark Here is the error: Warning: Cannot modify header information - headers already sent by (output started at C:Inetpubwwwroot eport.php:7) in C:Inetpubwwwrootgraysuit eport.php on line 38 Warning: Cannot modify header information - headers already sent by (output started at C:Inetpubwwwroot eport.php:7) in C:Inetpubwwwrootgraysuit eport.php on line 39 Warning: Cannot modify header information - headers already sent by (output started at C:Inetpubwwwroot eport.php:7)
written by spoco , November 20, 2008
Having the same issues with this working on screen, but does not export the file.
written by manmoji , December 02, 2008
How can we get records in different COLUMNS ??????
written by ss , December 02, 2008
1) how to rename a database in mysql
written by ricmetal , December 02, 2008
scripts works great here - added a btn Click to get backup on click, export. pretty nifty code here i am looking for a free simple viewer than will read the csv, any knowledge?
written by spbryant , December 09, 2008
When I use the script I get this in my .csv file in excel. Any idea what I am doing wrong or how to fix this? I am using the script exactly how it is posted above. Just adding correct access info for database. Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/mes/public_html/export-database.php on line 14 Notice: Undefined variable: csv_output in /home/mes/public_html/export-database.php on line 20 Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/mes/public_html/export-database.php on line 23
written by vvsl , December 10, 2008
hi i wrote the query like: "select firstname,lastname where firstName LIKE '%".$_POST['txtName']."%'"; to download into excel its not working but "select firstname,lastname where firstName LIKE '%satish%'"; its working fine what may be the problem
written by rathna , December 12, 2008
Hi, This code is very useful for beginners
written by Mark Russell , December 17, 2008
For anyone that has can not modify headers error this is generally because you edited the PHP code in an editor that is not designed to edit PHP. If you open the PHP file in the correct program, something like notepad you will notice that there is a couple of strange symbols at the very beginning of the code, if you delete these and save, then re upload it will work, if you need any help I am always willing to answer any questions. PS - Great script thank you!
written by ramien , December 25, 2008
My CSV file output contains 1 column with multiple rows. Each row is a new record, but the fields are not broken down into multiple columns like such A B C D mustang;red;1968; ford;blue;1988; harley;black;1999; Everything is contained with column A, how do I break up each field into a seperate column? MUCH APPRECIATED!
written by priyanka , December 25, 2008
hai, the above code which i tried is saving the file in csv format can any body give the syntax or code to excle file.
written by three11jeff , January 02, 2009
After looking at everyones solution to the "Cannot modify header information " error I still have not been able to resolve. I'm running the server on OSX. Would this cause any issues. Thanks.
written by pinakjit , January 05, 2009
Instead of showing data from a table into an excel sheet can we put data from a View into an excel
written by Kevin Mullarkey , January 08, 2009
I have a date column that appears as a text string in the csv output. Is there any way of automatically have it format as a 'date' columm so it will then sort by date correctly in Ms Excel? Many Thanks
written by Abs , January 11, 2009
hey gr8 script..thanks a lot.. but there's one problem, plzzz suggest me... one of my table contains 230445 rows, but max rows in excel is 65500 something...so how can i create many .csv files partially.... plzz. provide the code if possible...
written by Snack , January 18, 2009
I get this in Excel: Notice: Undefined variable: csv_output in C:wampwwweamexportCSV.php on line 16 Line 16: $csv_output .= $row['Field']."; "; Data is returned but it's a single row no columns. How to fix?
written by Bugeyes , January 20, 2009
Here is a slightly modified version which takes the column names from the results for use with more complex queries than a single table:
written by Bugeyes , January 20, 2009
Try again! $host = 'localhost'; $user = 'mysqlUser'; $pass = 'myUserPass'; $db = 'myDatabase'; $query = "SELECT * FROM myTable"; $file = 'export'; $link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error()); mysql_select_db($db) or die("Can not connect."); $values = mysql_query($query); $column_count = mysql_num_fields($values); for ($column_num = 0; $column_num < $column_count; $column_num ) { $field_name = mysql_field_name($values, $column_num); $csv_output .=$field_name . "; "; } $csv_output .= " "; while ($rowr = mysql_fetch_row($values)) { for ($j=0;$j < $column_num;$j ) { $csv_output .= $rowr[$j]."; "; } $csv_output .= " "; } $filename = $file."_".date("Y-m-d_H-i",time()); header("Content-type: application/vnd.ms-excel"); header("Content-disposition: csv" . date("Y-m-d") . ".csv"); header( "Content-disposition: filename=".$filename.".csv"); print $csv_output; exit;
written by Riya , January 25, 2009
this code really helped me.. replaced ";" with "," for a neat look
written by Harker , January 27, 2009
I try to open the CSV file after downloading it and Excel errors out. It will not open excel. Can you provide me with any ideas, I look at the data through NOTEBOOK and I can see the data. Thanks for any help.
written by Gustavo Atar , January 27, 2009
this is great one problem. On my Zip code entry the export function strips out the 0's in the zip codes. any help?
written by rashmi , January 28, 2009
hello i m getting all the fields of the table in one column. please help.
written by Gustavo Atar , January 28, 2009
replace - ";" with "," should work
written by lakshmi t , February 03, 2009
thanks a million! works like a charm..except that i want to export it to a csv while executing it as a command line script..Will try to figure it out.
written by iwa , February 06, 2009
what a great script!!..thankyou for saving my time..!!
written by proteus , February 10, 2009
I am still getting the table exported into one table when in Excel. I have changed the ';' to ',' ... but this did not resolve the issue. The file exports with two extensions, 'exportedTable.csc.xls' ... I expect this is from the header which tells the browser that this is an excel file. The only way I have been able to get excel to display correctly is to remove the 'xls' extension and then open the csv file with excel. It then displays perfectly.
written by proteus , February 10, 2009
apologies ... the above should have read : I am still getting the table exported into one **column** when in Excel.
written by proteus , February 10, 2009
sorry to jump the gun on those last ones guys ... I ended up just changing the header below: 'header("Content-type: application/vnd.ms-excel");' to: 'header("Content-type: application/csv");' It now works like a charm ... so long as the user downloading the file has excel set as their default .csv viewer ;-) Thanks guys!!
written by DanielRo , February 10, 2009
Hey proteus, I am sorry, but I could not find the time to investigate your posted problem. Nonetheless, thank you for posting your solution to the community!
written by deano , February 18, 2009
hi daniel, great script! much better than the 5 or so others i've tried to use tonight. one question i have was answered in part above... but i'm still having a problem. if the mysql data has returns, commas and quotes, how can i ensure that each record stays on one line? i have included a sample record below: e.g. "40","2008-08-20","15:38:13","name removed","email address removed","phone number removed","Newport","3015","Australia","HI, Looking for a beach trolley for a 14.5 ft sailing dinghy... I am assuming that the "Trolley-cradle" is what I am after... do you know where I could get one? regards Owen ","","1","STEVE - EMAILED QUOTE",
written by DanielRo , February 18, 2009
@deano You can place single quotes around your expresions. Replace $csv_output .= $row['Field']."; "; with $csv_output .= "'".$row['Field']."'; "; Hope this helps.
written by deano , February 19, 2009
hi daniel that seems to be for the field name lines, should i also alter this line later on: $csv_output .= $rowr[$j]."; "; it seems to be the line by line output that is causing the problems with the csv. thanks
written by deano , February 19, 2009
maybe i need to do something like strip out any or similar characters that are returned in the query before i try and output to csv?
written by DanielRo , February 19, 2009
@deano You are right, my first solution refered to the second $csv_output like you mentioned. As for striping, you can use PHP's htmlspecialchars used to convert special characters to HTML entities. In this case you need to replace $csv_output .= $rowr[$j]."; "; with $csv_output .= htmlspecialchars($rowr[$j], ENT_QUOTES)."; "; I am positive this will solve your problem.
written by deano , February 20, 2009
thanks daniel that works for the quotes, but not for the carriage returns with the slash and the n (i tried to show when i posted here above but it didn't show on the page). i've actually been looking at exporting xml instead of csv - has the added advantage of being able to include a whole cell between the relevant xml tags regardless of what's in them. you don't have a similar piece of code lying around to do that for me do you :) otherwise i can create it manually by looping through every line and creating the right tags that way. i appreciate you help!
written by Zsolt Szekely , February 25, 2009
Hi! I have an issue with script building. I have a csv file with lot of fields. I wish to import in more than 1 table the file. Is that possible to do it somehow?
written by lisagt , March 01, 2009
Daniel, thx for the script. I have not treid it yet...as I am looking for one to only export the data in csv since last download. Any ideas on doing this please? Thank-you, Lisa
written by Christopher Reichert , March 02, 2009
Hi there, Great Script. I am hoping you can help with gathering information from multiple tables in the same DB. I saw an entry: $result = mysql_query("SHOW COLUMNS FROM ".$table1." , ".$table2.""); $i = 0; if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_assoc($result)) { $csv_output .= $row["Field"].", "; $i ; } } $csv_output .= " "; $values = mysql_query("SELECT * From ".$table1." INNER JOIN ".$table2." ON ".$table1.".custcode = ".$table2.".custcode"); but got a little lost. My DB has 4 tables and I'd like to be able to grab all the data from each and dump into one CSV/XLS file. Many thanks.
written by Timir , March 13, 2009
It rocks man. Thanks for this Post.
written by yaser , March 15, 2009
hi , mySQL database contain Arabic Characters when i download the excel file and open it, it doesnt understand these Characters so whats the solution
written by hoodmayor , March 24, 2009
I need some help creating a sql script that will create a xls tab for each table name that ends with '_D' and dump all the data in the same format as the sql table. Please help.....Thanks.
written by AC , March 25, 2009
It seems to work but I get no output? It brings up excel but there is no columns of data?
written by AC , March 25, 2009
I am getting this error? Can not connect.Access denied for user: 'dev23z@localhost' (Using password: YES)
written by AC , March 25, 2009
I got it to work but I only want to download one field in the table not everything. $values = mysql_query("SELECT email FROM ".$table.""); I only want email but it give me everything?
written by nagaraja , March 30, 2009
hi all , thanks for the script problem is that i want print it in txt/word format/xls format or any other format .how do i do that and i want to save it in some folder on mycomputer automatically .Can i do that ,if yes pls suggest me with the source code thanks in advance
written by Karl , April 08, 2009
I have some NJ zipcodes in my data which start with a 0 and when i go to export the data to a file using this awsome script it strips out the first 0 in the zip code... can anyone tell me how to fix this plz?
written by marb19 , April 14, 2009
Hello, When I use the script I get this error: No input file specified. Does anybody knows the reason? Thanks in advance.
written by Russell , April 24, 2009
Hi all, when I run the script the result display on the screen but no file is downloading. Any ideas? thanks!
written by huzefa , May 05, 2009
I am getting the output on my screen, not in a downloaded csv file ...can anybody please help me out ..its very urgent.....
written by Vaibhav Gupta , May 05, 2009
its working but the problem is: when opening it showing all the field values in one column... help me plz
written by huzefa , May 05, 2009
sumbody plz reply if have any soultion about my problem........... I am getting the output on my screen, not in a downloadable csv file ...can anybody please help me out ..its very urgent.....
written by buzz , May 13, 2009
hi i need help.. how do i move the columnn in the excel.. (from the database table) please help! e.g. name | email | friendsemail |dateadded | | value1 value1 This e-mail address is being protected from spam bots, you need JavaScript enabled to view it This e-mail address is being protected from spam bots, you need JavaScript enabled to view it may 12, 2009 how can i move the dateadded column? please help
written by NKhan , June 04, 2009
Hi, Nice article. One thing i have been searching for though is that if data in mysql table is non english[unicode to be more correct] like arabic for example then what changes needed in this code so that the data appears in correct language in excel file as well. Please help, it is driving me crazy as i can't find a clue. Regards Khan
written by ffcamaro9 , June 05, 2009
I also had the problems downloading to excel only in IE...always worked in FF. Here's what I used to get it working. Just added the pragma value to public. header("Pragma: public");
written by tracy1987 , June 16, 2009
i have a question. How to export data to multiple-sheet into same excel file? I'm using postgrade as my database.
written by Kevin Marx , June 19, 2009
Daniel, Great Script. I would like to know if its possible to format the exported data (ex having one column in red color, second column in blue). Any help inputs would be greatly appreciated. Cheers kevin
written by Maciek , June 20, 2009
I was really helpfull, i was jus looking for it like 6.. 7 hours.. Cheers !
written by Pierre , June 22, 2009
Nice script! In order to have excell process it in the right way, without messing around with double quotes, I have changed the following: // Added line: $tab .= chr (9); // Before line: $result = mysql_query("SHOW COLUMNS FROM ".$table.""); // Changed the following 2 lines: $csv_output .= $row['Field']."$tab "; $csv_output .= $rowr[$j]."$tab "; Now you'll have a tab delimited file :)
written by jibin , June 25, 2009
Hi, Great Script. I would like to know if its possible to format the column in colors thanks, jibin
written by Anne , July 03, 2009
I have a question about using a WHERE statement. I want to use this script to download a specific row from a table. It works fine as long as I dont have the WHERE statement in there but it dumps the entire contents of that table. I get an error that the variable I am using is not declared or I get that my statement is not valid. $values = mysql_query("SELECT * FROM ".$table ." WHERE oderID =" . $_SESSION['orderID'].""); this is what Im trying to do(above is line 32) and this is the error i get if i do that Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:wampwwwSitecsv2.php on line 32 if I put it this way $values = mysql_query("SELECT * FROM ".$table ." WHERE oderID =" . $_POST['orderID'].""); I get this error as well as the first one Notice: Undefined index: orderID in C:wampwwwSitecsv2.php on line 31
written by Anne , July 03, 2009
ok i fixed it myself. if i use a button in the form it submits the post variable to the csv.php script and it inserts only the record that i want in the excel file. yeah!!!!! code here if anyone else would like to know how to do it. this code will pop up a dialog box for you to save the file where you want. it has an auto name that uses the date/time stamp to name the file but i guess you could change that to what ever when the dialog box pops up. this is the code from the main page. Order ID mycsv.php file exportcsv.inc.php file [code]
written by Anne , July 03, 2009
ok good fun it didnt post my code :) trying again this is the code from the main page. Order ID mycsv.php file exportcsv.inc.php file
written by Anne , July 03, 2009
ok apparently i cant put the code in here
written by Aaron Chakona , July 07, 2009
Hie guys i'm new to Php. where in the script do i put replace ";" with ","
written by RaySpike , July 20, 2009
Nice script, just like a few others : The output is shown at the page, but no file to download will popup
written by rook_rooster , July 24, 2009
If you are having problems with the columns being all in the first column, change... $csv_output .= $row['Field'].";"; $csv_output .= $rowr[$j].";"; to... $csv_output .= $row['Field'].","; $csv_output .= $rowr[$j].",";
written by Sam_the_man , July 29, 2009
Nice script, but I was looking for something more specific. I found a nice thorough tutorial for what I needed here: http://geneomatic.com/wordpress/?p=1357
written by Arevik , July 30, 2009
Hi, My script is printing the output to the screen, not to a downloadable csv file, I didn't change anything except the table name, am I missing something?
written by Suryo P , August 10, 2009
Hi all, can somebody give me a hand... I've used a html form to get the dates as reference to retrieve the values from the db but when i used to retrieve the dates on a range it only showed the columns... for single date I used as below: $values = mysql_query("SELECT * FROM ".$table." where date='$_POST[date]'"); changing the '$_POST[date]' with a variable also worked nice but when i tried to use BETWEEN command it failed... $values = mysql_query("SELECT * FROM ".$table." where date between '$_POST[s_date]' and '$_POST[e_date]"); Best Regards
written by Asa , August 11, 2009
Hints: When opening delimited files in Excel, try opening Excel first then using File > Open. Let's you choose the correct data format for each column and avoid things like "123,234,345" turning into 123234345 or "01234 567 890" turning into 1234567890. 'Text' format being the safest. To remove new lines / carriage returns from the exported data you can do something like this for each field: $csv_output .= str_replace(array(" "," "," "," "," ","x0B"),"",$rowr[$j])."; "; When exporting data from multiple tables there are many options available such as using joins and nested select statements but this probably has to be the simplest way: SELECT t.field1, t.field2, o.field_a, o.field_b FROM tablename AS t, othertable AS o WHERE t.field1 = 'value' AND t.field3 = o.field_c; Now what I'm interested in is buffering. When I try to export 500,000 rows of data, which would create a csv file of about 3MB, the script requires around 400MB of memory. Anyone have a simple solution to this?
written by Asa , August 11, 2009
Ok, some of the characters got removed: $csv_output .= str_replace(" "," "," "," "," ","x0B"),"",$rowr[$j])."; ";
written by Asa , August 11, 2009
Still getting removed. Sorry - The array values should be: 1. A non-breaking space ( ) 2. A tab character (backslash t) (t) 3. A carriage return (backslash r) (r) 4. A new line (backslash n) (n) 5. Another new line (backslash little x zero) 6. A vertical tab (backslash little x zero big b)
written by Hilton Becker , September 01, 2009
Hello, This is a great script. I seem to get 4 fields output instead of the 3 that are in my table. 3 with names and one blank. Is this normal, or should there just be 3. regards Hilton
written by Tom Worman , September 18, 2009
Hi, My slightly adapted script is as follows: Now this works fine in terms of producing a CSV file however I have two problems... 1) The first row is blank, this doesn't mean that it's missing any data but it just inserts a random line of commas 2) Each row contains additional commas on for values which are non-existant. Where am I going wrong? , , , , , , , Membership ID, Name, e-mail address, phone number, , , , Membership ID, Name, e-mail address, phone number, , , , Membership ID, Name, e-mail address, phone number, , , , Membership ID, Name, e-mail address, phone number, , , , Membership ID, Name, e-mail address, phone number, , , , Membership ID, Name, e-mail address, phone number, , , , Membership ID, Name, e-mail address, phone number, , , ,
written by Tom Worman , September 18, 2009
Hi I realised I didn't include the code... rinse and repeat, My slightly adapted script is as follows: // WORKING CODE SPITS OUT CSV FILE $host = 'localhost'; $user = 'upsuweb'; $pass = 'd42bmjv7'; $db = 'upsuweb'; $table = 'jos_users'; $file = 'export'; $link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error()); mysql_select_db($db) or die("Can not connect."); $query = "SELECT jos_comprofiler.cb_aumembershipid, jos_users.name, jos_users.email, jos_comprofiler.cb_mobilenumber FROM jos_users INNER JOIN jos_comprofiler ON jos_users.id = jos_comprofiler.user_id WHERE cb_athletics = 1"; $result = mysql_query($query) or die('Ooops, query failed'); $i = 0; if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_assoc($result)) { $csv_output .= $row['Field'].", "; $i ; } } $csv_output .= " "; $query = "SELECT jos_comprofiler.cb_aumembershipid, jos_users.name, jos_users.email, jos_comprofiler.cb_mobilenumber FROM jos_users INNER JOIN jos_comprofiler ON jos_users.id = jos_comprofiler.user_id WHERE cb_athletics = 1"; $result = mysql_query($query) or die('Ouch, query failed'); while ($rowr = mysql_fetch_row($result)) { for ($j=0;$j
written by Helen123 , September 23, 2009
Hi , I am actually new to coding and I wanted some help with the export to csv file from postgresql. ANyone Help !!!!I tried using this code and makin changes but didnt really get it right ..
written by Victor H. , October 15, 2009
Nobody answered one of the most excited question!!! If we wanted to export only a few fields of the table, the first line of the csv will filled with all the field's names!!! but is it possible to show columns (only the desired thable fields)??? or could you somehow remove the show columns (the first line of the cvs)?????????????
written by Budget Web Company , October 20, 2009
Really useful script. We'll definitely be using it! Thanks!
written by Dublay , November 03, 2009
Help, the script outputs the full php-source to my csv-file. After that bunch of code I've got my correct table. Any ideas?
written by hhhh111 , December 11, 2009
sir, I want to know how we show only some colum fields in the database in Excel sheet[not all the field in the database].Could please help me.
written by vibrantdigital , January 08, 2010
this script works great in Firefox, but in Internet Explorer I get this error window: "internet explorer cannot download export.php from mydomain.com. Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later." Can anyone help me get past this compatibility issue?
written by Pablo Rattin , January 14, 2010
and now how i send the csv to a mail? Thanks a lot
written by Ben W , January 19, 2010
Script works like a charm, but is it possible to save the output to a directory instead of saving a local copy?
written by Cass Khav , January 31, 2010
I have the exact same question. Can it be saved to the directory on the server where it is running instead of saving as a local copy. Thanks!
written by SK , February 02, 2010
This was a big help. Thank you.
written by shakti , March 17, 2010
realy nice
written by sandeepnassa , March 30, 2010
I want to export data in different csv sheets. How i can do this. File would be one but there would be multiple sheets.
written by DeeZulu , April 06, 2010
i am using XAMPP server, i ve changed the variables as below $host = 'localhost'; $user = 'root'; $pass = ''; $db = 'emp'; $table = 'emp_master'; $file = 'export_csv.csv'; still i am not able tyo download the CSV file simply it display the output along with warnings. how to solve this... below are the warnings that i am getting:- Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'Asia/Calcutta' for '5.5/no DST' instead in C:xampphtdocstest5_04_2010csvx.php on line 31 Warning: Cannot modify header information - headers already sent by (output started at C:xampphtdocstest5_04_2010csvx.php:31) in C:xampphtdocstest5_04_2010csvx.php on line 32 Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'Asia/Calcutta' for '5.5/no DST' instead in C:xampphtdocstest5_04_2010csvx.php on line 33 Warning: Cannot modify header information - headers already sent by (output started at C:xampphtdocstest5_04_2010csvx.php:31) in C:xampphtdocstest5_04_2010csvx.php on line 33 Warning: Cannot modify header information - headers already sent by (output started at C:xampphtdocstest5_04_2010csvx.php:31) in C:xampphtdocstest5_04_2010csvx.php on line 34
written by Dawn Drury , April 07, 2010
THANK YOU to DanielRo and all those who commented! Between all this info on the page I was able to my attempt at this to work. THANK YOU!!!
written by Billa , April 08, 2010
This Code is very helpful to me thanks for providing this type of easy code to export mysql data in to excell
written by Tony Liong , April 22, 2010
Hi... your code is helpful. But I need some more advance about your code. Can I use this coding script for multiple sheet too? Thanks before
written by rad , May 04, 2010
Hi, for some reason it isn't exporting for me but I am not getting any errors either. What am I doing wrong?
written by Tutorials99 , May 26, 2010
your code is helpful. keep it up i found another site having Fantastic Page Rank Professional tutorials see link below tutorials99
written by pxlr.de , June 04, 2010
if you have some problems with special characters (for example ä,ü,ß ...) change print $csv_output; to print utf8_decode($csv_output); Thanks for sharing greets
written by pxlr.de , June 04, 2010
By the way .. how can I download the file via a Link Database export ?? Thanks
written by manoji , June 07, 2010
I wrote a function to download data in to excel.Now I want insert 0 before other numbers in cell.Can u help me
written by Glenn P , June 08, 2010
Thanks I ahve done this on 2 servers, one server extarcts data in UTF-8 format, the other is in ASCII. I want the .csv in UTF8 encoding on both servers. Both server are set up identicle from what I can see. Any idea's where I am going wrong ?
written by naveenkongati , June 11, 2010
Thanks for nice script. My requirement is matching with the script but some changes need to be made. Instead of MySQL i need to get the data from Oracle and delimiter should be used as tab and also this needs to be mailed directly with csv file as a attachment.Any idea please. Thanks
written by Steve_Myers , June 15, 2010
Loving this script but I'm hoping someone can help with with an FTP element that will upload the generated CSV to an FTP directory. Any assistance is much apprecaited. Thanks
written by Travis , June 15, 2010
I would greatly appreciate some help on a sql query, this is what I use on phpMYadmin bit I can?t seem to get it to would on here? SELECT * FROM products_description INNER JOIN products ON products_description.products_id = products.products_id INNER JOIN manufacturers ON manufacturers.manufacturers_id = products.manufacturers_id;
written by Alex_Sin , June 18, 2010
Hello everybody. Very good script.I use it.But I have one problem. When user fills "comments" field in request form on my website he writes text with commas and carriage returns.This text goes in to sql.When I make export to csv all fields are going to the right place.Only field "comments" takes few rows, because of commas and carriage returns(As I think). How can I fix whole text in one cell. Thanks a lot in advance.
written by Jay Cleary , June 18, 2010
Hello all, I need somebody to help me do 3 things with my online MySQL database: 1. Import a text file stored locally into a table in the online MySQL database. I'm using the LOAD DATA LOCAL INFILE command now but I can't save that in a stored routine so I've just been using stored SQL scripts. 2. Export query results from a table in the online MySQL database to a local text file. 3. Export query results from a table in the online MySQL database to a text file and send via email. I need this done right away so please email me at jay@dotcomparts.com if you are interested in the work. Thanks, Jay
written by marnie , June 19, 2010
I am trying to use your script, but one difference and not getting the file. The one difference is that I'm only writing out one column from the table so I don't need the column names. Here's my script $query = mysql_query("SELECT distinct(enroll_email) FROM enrollment where enroll_email is not null and enroll_email ' ' order by enroll_email"); $file = 'export'; while($row = mysql_fetch_array($query)) { $enroll = $row['enroll_email']."n"; } $filenmae = $file. "_".date("Y-m-d_H-i",time()); header("Content-type: application/vnd.ms-excel"); header("Content-disposition: csv" .date("Y-m-d") . ".csv"); header("Content-disposition: filename=".$filename.".csv"); print $enroll; exit; Two things wrong. 1. I'm only getting the last value 2. It is printing it into the browser. Where is the file being written to? I don't want it to the browser just to the file.
written by anoop121 , June 20, 2010
Good Code, helps a lot thank you
written by marcjae , June 22, 2010
Worked like a charm. Some minor tuning was required though. All in all, got it up in two minutes. SO its THAT GOOD. Thanks I know it's more of a hack than a fix, but works; if (empty($csv_output)) { $csv_output = ''; } Removes that nasty little notice
written by Gowtham , July 05, 2010
I need to print the data in separate columns and not in same column pls help me out
written by dchang , July 13, 2010
Thanks a ton for your code - it has helped tremendously. I was wondering if it is possible to autoformat the columns in Excel somehow? I know it's been asked here before, but I didn't really see a response. Specifically, I'd like to just extend the width of the column to fit overflowing table cells. Thanks!
written by Vandana Gupta , July 22, 2010
thanks a lotz.......for such a simple code, but i want to know is there any way to grant read only permission to the exported csv file. As i want to keep the exported data not to be overwritten.
written by SHWEBDEV , July 29, 2010
Ernest, Bagyaraj and anybody else having problems with commas in their data causing problems in comma delimited files. You need to escape the comma! This did the trick for me: Try adding this function to your page. function escape_csv_value($value) { $value = str_replace('"', '""', $value); // First off escape all " and make them "" if(preg_match('/,/', $value) or preg_match("/n/", $value) or preg_match('/"/', $value)) { // Check if I have any commas or new lines return '"'.$value.'"'; // If I have new lines or commas escape them } else { return $value; // If no new lines or commas just return the value } } and replace your CSV output with: $csv_output .= escape_csv_value($rowr[$j]).',';
written by anurag , August 04, 2010
how we save this excel file
written by DtsJoe , August 09, 2010
Is there any way, the exported file will be stored in alocation of the server and and after finishing the export a link will be emailed to a specified email address to download the file...Can you please help me with this
written by Prech Gosma , August 17, 2010
i tired it and it worked perfectly, i have a problem, i am dealing a table with encrypted pin values, and i need to decrypt the these values whilst exporting it to the excel file, i am trying to resist the temptation of creating another table for decrypted pins during the generations of the pin values. i just need a hack to work on one off the columns differently.
written by Rahul Singh , August 22, 2010
god bless you. spent hours trying to do this lol it took me 2mins after finding this. thanks :)
written by piero , August 25, 2010
hi i can download the csv file (i would like download in xls) but i got an error when i want open on microsoft excel but everything is good when i open on apple numbers what can i do?
written by annap , August 26, 2010
To force a file download I've found that changing the following line works: // original header( "Content-disposition: filename=".$filename.".csv"); // replacement header( "Content-disposition: attachment; filename=".$filename.".csv");
written by imran612 , August 26, 2010
Hi all, i have to import the csv into mysql...plz help me i hv new to programming..
written by shone lee , September 02, 2010
good script..thanks alot. Do you need more help? Ask now!
|
|
| Last Updated ( Wednesday, 28 May 2008 ) | |
Every AdSense Publisher had nightmares when hearing of all the horror stories where people get banned from AdSense because someone else used their AdSense code on malicious sites, breaking the Google AdSense TOS.
In this Photoshop tutorial you are going to learn how to add a bump to a photo increasing its artistic effect and making it more realistic.
