This post is a tutorial on how to export an HTML table in CSV format using a PHP script. The table is generated with data from a MySQL database containing orders, products and totals. I’ve dummed down the database and php code so it is easier to understand and will walk you through each step required to go export a table of orders in the database to CSV. It is true that you can do this directly from phpMyAdmin, but the point of this post is to teach the reader how to do it themselves. It took me a day of fiddling around and searching various forums and wished an article like this had been around then.First you’ll need a PHP development environment to test your application. You could use Adobe Dreamweaver, but if you’re on a budget like me you’ll probably like Komodo Edit and XAMPP. Both are free of charge and will do the job. The goal of this exercise is to quickly turn an HTML table into a CSV file we can later open with Excel or some other charting program.
[updated 3/11/13 for Brecht] To follow along, simply download the database SQL and PHP source files below (right-click & save target as… to download):
[Step 1] Now that you’ve got all the files, you’ll need to import the SQL file into a database called test using phpMyAdmin (provided with your XAMPP installation). Next you’ll need to copy the 2 php files into your htdocs directory or a subdirectory within hdocs in XAMPP. Open each file for editing and fire up your XAMPP control panel to launch Apache and MySQL.
NOTE: My test environment uses a blank password for the root MySQL account to authenticate to the database. If your setup uses a different password you’ll need to modify the blank password in table.php.
[Step 2] Navigate your browser to http://localhost/table.php or http://localhost/subdirectory/table.php and you should see a table of values. At the bottom of the page you’ll notice a button that says “Export table to CSV.” Clicking it should pop up a CSV file with the same contents as the table for you to save.
[Step 3] The basic concept of this export is to create a variable (I named it $csv_output) and to store all of your data in it separated by comma’s (,) and newline characters (n).
[php light=”true”]$csv_output .= $row[‘orders_id’] . ", ";
$csv_output .= $row[‘value’] . "n";[/php]
To jump to a new cell I append a comma (,) and to jump to a new row I append a newline character (n).
[Step 5] The only thing left to do now is to get my CSV variables ($csv_hdr and $csv_output) from the table.php file to the export.php file. To do this I simply use an HTML form and 2 hidden fields.
[html light=”true”]</pre>
<form action="export.php" method="post" name="export"><input type="submit" value="Export table to CSV" />
<input type="hidden" name="csv_hdr" value="<? echo $csv_hdr; ?>" />
<input type="hidden" name="csv_output" value="<? echo $csv_output; ?>" /></form>
<pre>
[/html]
Now I can simply get the contents of my variables using $_POST in my export.php file, and continue with exporting my CSV file.
[php light=”true”]
<!–?php /* This file will generate our CSV table. There is nothing to display on this page, it is simply used to generate our CSV file for download in our browser and then exit. That way we won’t be re-directed after pressing the export to CSV button on the previous page. */ //First we’ll initialize an output variable for the content of our CSV file. $out = ”; //Next we’ll initialize a variable for our filename prefix (optional). $filename_prefix = ‘csv’; //Next we append our POST header data from table.php and append it to out. if (isset($_POST[‘csv_hdr’])) { $out .= $_POST[‘csv_hdr’]; $out .= "n"; } //Then we grab the table data from table.php and append it to out. if (isset($_POST[‘csv_output’])) { $out .= $_POST[‘csv_output’]; } //Now our out has nearly all the parts of a file, we just gotta create/name it. $filename = $filename_prefix."_".date("Y-m-d_H-i",time()); //Generate the CSV file header header("Content-type: application/vnd.ms-excel"); header("Content-Encoding: UTF-8"); //Added to deal with UTF character support header("Content-type: text/csv; charset=UTF-8"); header("Content-disposition: csv" . date("Y-m-d") . ".csv"); header("Content-disposition: filename=".$filename.".csv"); echo "xEFxBBxBF"; // UTF-8 BOM added for UTF character support //Print the contents of out to the generated file. print $out; //Exit the script exit; ?–>
[/php]
That’s it folks. That’s how to export an HTML table to CSV in 5 easy to understand steps.
Comments
88 responses to “Convert an HTML table to CSV using PHP”
Thanks David,
A lovely, simple, set-by-step explanation of something I have just spent the last 4 hours trying to find via web-search.
Everything worked well except that if the user enters a comma in the text the results get pushed across one column.
Kind regards.
That’s a good point, I guess you could use string replace to eliminate commas in the user input…something like str_replace(“,”, $value). Glad you found my tutorial overall useful 🙂
Hi,
great script, have the same comma issue with our data, and want to use the string replace function but not sure how to implement it on your script.
Would be very grateful for your advice.
Thanks
Pete
Really excellent and simple script! I did require the str_replace function and added full php tags (short tags are turned off in my dev environment and production sites) but other than that, it was easily adaptable to my projects, which ALL require html tables that are exportable as .csv.
Kudos!
THE BEST!!!
THANKS A LOT FOR POSTING THIS GREAT SCRIPT!
THANKS! REALLY HELPFUL!
This is just so cool and exciting – the script that best suited my needs that I came across in quite a few hours of searching – well done! Just needed some fine-tuning like catering for missing values that led to data going into the wrong columns.
@Rose the doze
Sorry – no missing values – I had commas in some of the data so will have to use str_replace etc. No sweat
OMG thanks thanks a lot, works, i dont believe, i make the changes to my database and table and woooorks..
You doesnt have an idea how much this script help me to my work..
Thanks, thanks.
How can you define a saving path here?
Jeff,
Do you mean the path where the downloaded file will be saved? If so, there’s no way you can set that since it would be something each user would specify based on their particular browser/operating system.
David
Dear David,
I downloaded your script with a smile on my face but unfortunately it’s not saving an HTML table to CSV data. You should change the title, I think.
Huh, I guess you’re right. Never thought about it like that because I had the html table coded up before the exporting csv stuff :).
Hi David,
Had been searching the net for a couple of hours on how to do this and was about to give up when I came across your site – appreciate the sharing… new to php & mysql and having complete examples with comments really helps…
Cheers
Perfect! Worked like a charm. Thanks man.
Great script! Fantastic, very nice. I just have one small problem and I hope you can advise.
I’m new to PHP. I’ve built a PHP webform and linked it to an SQL Server 2005 database. User completes the webform and submits it. They have the option of doing a search by Date to see specific data. The also have the option of exporting the data to an Excel spreadsheet. We’re only talking about a little data – only 5 columns and only 6 or 7 rows of data.
Since I’m using PHP and SQL Server, I’ve had to tweak a few MySQL things to get it to work. I’ve almost got the Excel export working. However, I can only get the column headers and the first database record to display in the Excel spreadsheet. The other four or five records won’t display, only the first. I had problems getting $csv_output to work and had to really tweak and move it around to get it to work and diplay a record.
Would you mind taking a look at the code below and show me a better way of doing things. I know what is causing only one database record to export, but I just don’t have the PHP background to get all the records to export.
<?
$connection_string = 'DRIVER={SQL Server};SERVER=EBSPSQL3EBSDSS2;DATABASE=PPAD';
$user = 'PPadUser';
$pass = 'rmot22';
$connection = odbc_connect( $connection_string, $user, $pass );
$term = $_POST['term'];
$result=odbc_exec($connection,"SELECT CONVERT (char(10), submitdate, 101) AS submitdate, stationid, serialnum, empnum, category, type, description FROM patriot1 WHERE submitdate = '$term'");
$trans = get_html_translation_table(HTML_ENTITIES, ENT_QUOTES);
$results_found = false;
while ($row = odbc_fetch_array($result)) {
$body = str_replace('+',' ', urlencode(stripslashes($row['description'])));
$submitdate = substr($row['submitdate'], 0, 10);
$csv_hdr = "Station ID, Serial Number, Submitted By, Category, Type, Description";
$csv_output0 = $row['stationid'] . ", ";
$csv_output1 = $row['serialnum'] . ", ";
$csv_output2 = $row['empnum'] . ", ";
$csv_output3 = $row['category'] . ", ";
$csv_output4 = $row['type'] . ", ";
$csv_output5 = $row['description'] . ", ";
echo "n”;
echo “.n”;
“n”;
echo “n”;
echo “Daten”;
echo “Station IDn”;
“Serial Numbern”;
echo “n”;
echo “n”;
echo “$submitdaten”;
echo “$row[stationid]n”;
echo “$row[serialnum]n”;
echo “n”;
echo “n”;
echo “Submitted Byn”;
echo “Categoryn”;
echo “Typen”;
echo “n”;
echo “n”;
echo “$row[empnum]n”;
echo “$row[category]n”;
echo “$row[type]n”;
echo “n”;
echo “n”;
echo “Descriptionn”;
echo “n”;
echo “n”;
echo “$row[description]n”;
echo “n”;
echo “”;
$results_found = true;
}
if (!$results_found)
{
echo ‘No Results Found.‘;
}
?>
Hi David,
It looks like you are generating just one line with your PHP output in the area where you are echoing out the following:
Really what you want to do is utilize a loop to keep appending to your output. In PHP the . (dot) is used to append strings, so that is what I’m doing in these lines:
$csv_output .= $row[‘orders_id’] . “, “;
As you can see in the line above there is a dot infront of the equal sign which means add the following stuff to my $csv_output. Each time the loop goes over this statement whatever is in the orders_id row will be appended to $csv_output separated by a comma so you’d get this:
Row ID
1,2,3,4,5….
If you statically generate an output like what you’ve done:
echo $row[‘orders_id’]
You’ll get just the first entry in the array. To get all of them you’ll need to create a loop such as a while or for that will iterate through each row and spit out an orders_id for each row.
while there are more rows in our SQL query, do the following or:
while ($row = mysql_fetch_assoc($result)) {
echo $row[‘orders_id’];
}
Hope that helps…
Here’s how I had to alter the export script to get it to work with SQL server. I’m sure there is a better way.
I still can’t get the second row of database records to export to Excel….
Forgot to paste the script….
Nice tutorial….Tq
Excellent script! Works great.
One question: Can PHP automatically format the fonts and column background colors when you export or does that require a 3rd party software (i.e. PHPPear or something similar)?
Mike,
A CSV file by definition won’t have any colors or column background because it just contains (C)omma (S)eparated (V)values or data. However you could export to an HTML file that contained within a simple table and with within the rows of that table you could apply formatting such as unique background color to every other row or column…things of that nature. If you wanted to export with row/column colors to a Microsoft Excel spreadsheet, you’d have to figure out the structure of that type of file first before you can build it with PHP. I’m not a Microsoft Excel expert so you’ll have to find how to do that somewhere else on the Internets.
Good luck!
This is perfect – thanks. Wish I’d found it sooner!
Hey David,
I cant seem to get your code to work…. i keep getting commented out text printing on the screen.
thank you very much this was needed…..tons of thnx!!!
It saved me a lot of hard work. I was searching for a guideline that can convert table to csv. Thank. Neaz
This is the highlight of my week. I think I love you. Thank you.
Why not simply use
$html = ‘MyTable’;
$html = strip_tags($html, ”); //Remove all html except and
$html = str_replace(”,’,’,$html); //Replace with commas
$html = strip_tags($html); //Get rid of remaining html
?
Ok, you might need a couple more lines for inserting line breaks and to get rid of commas at end of lines, but that does what all your php does doesn’t it, without basically just repeating the concatenation code for both html and csv?
Ok, well my comment doesn’t make much sense after all the html has been stripped out, but maybe you can guess where it goes!!
Hi,
Thank you for this script!!
Is it possible to run this script with a cron command?
How do i need to set this up?
greetings,
Dhoom
Thank you very much dude… It’s working fine, but you need to add some minor thing to add your script. That is instead of using this “<?" notation better use "<?php"…..:)
Thanks.. Good Post…..
Hah, yes. that is a good idea. <? can be confused with XML document. I'll fix that.
Hi David,
Your codes are just amazing. But i have a situation here, I have a PHP page where i am showing more than 5000 rows with pagination.
What about the PHP Table with pagination? How to download the entire table?
David first and foremost – excellent job on this tute – I had to make some changes to suit my purposes, but due to your clear explanation and clean layout it went without a hitch. I have a request if at all possible in that when you click on the download button for excel doc that it will also trigger a send email function that can grab this excel doc that is being made on the fly and send along either in the body of an email or as an attachment. If the doc was being saved to the server I would have no problem in setting this trigger, but since it is being created on the fly I do not know how to grab it (if possible) – thanks in advance and great job once again!
Hi J Pena,
Sending a file via email is controlled by the operating system and from a browser I do not have the ability to initiate that sort of a “trigger”. What could be done with the code is that rather than popping up the file for download, the file is stored stored in a variable and then the page re-directs to an email form. The email form accepts a POST variable containing the file and attaches that to a message the user can send from the form. The problem with this approach is of course that you won’t have an address book or the convenience of your email program in an email form. You’ll have to enter all the information manually.
Hope it helps,
David.
for those european characters that get messed up in csv, encode the final sting with this:
//these 2 invisible characters will make correct encoding to excel
echo chr(255).chr(254).iconv(“UTF-8”, “UTF-16LE//IGNORE”, $data);
?>
Very nice! Thanks for the tip.
Just wanted to let people know that if your first column header is ‘ID’ in capital letters, like many database tables, then a SYLK file format will be created. This is a bug microsoft knows about and had me scratching my head for a bit.
Hello. Thank you for the script, but there is some problems (maybe on my end)
1. It gives me ” Undefined variable: csv_output ” // Tried taking out the dot, but it will give me the last value
2. Still the headers are imprinted (actually what’s below in table.php) // Even in the original file i’ve got from you
Tried strip_tags, but in vain.
Waiting for a answer, or something.
Tyvm !
It seems that what’s imprinted in the csv file is this ” ( ! ) Notice: Undefined variable: file in C:wampwwwAExcelexport.php on line 14 ”
If i delete the $file , in export.php, then when opening i get some kind of errors about filetype..
Delete the comments, i’ve fixed it. Thank you very much ! Cheers!
Hey even i am getting the same error,can you tell me how did you fix it?
He Man,
This is great! It’s working like a charm 🙂 Very happy here!
Thanks.
Bravo, sir. Working like a charm 🙂
Fabulous script! I’m thinking, however, that the purpose might be served better by $csv_output = join/glue/implode($row,”,”)? To this point, you could do the same for the headers if you had them stored in an array rather than listed individually. Perhaps combine the names of the fields from the db with mysql_field_name(), pushed into an array and also join’ed?
Of course, you’d still have to pre-process some $row members to clean them up (if necessary).
Just my 2 cents. 🙂
—–Mike
March 28, 2011 at 11:18 am
One question: Can PHP automatically format the fonts and column background colors when you export or does that require a 3rd party software (i.e. PHPPear or something similar)?—–
@Mike: There is a great OOP script called PHPExcel that expertly outputs any data to a true Excel spreadsheet (even a PDF) with all the formatting you can dream up. The learning curve is steep but once you’ve mastered it, it’s awesome!
Hope this helps.
Thank you for sharing and using a good example showing calculations as well.
One of my fields is decimal(5,2) and fields showing “0.00” in the db and on the php table are not exported, but any number e.g. “6.25” is exported to csv.
Any ideas on how to export and show the “0.00” otherwise you have rows of empty Excel fields.
1, John, , 22 // note space between commas.
2, Tom, 6.25, 23
Still a great script after three years! Thank you.
Couldn’t have asked for more. This is so on-point. Thanks a lot!
great script..my only issue is I have data which contains commas but for some reason when I wrap the fields in quotes, nothing but the headers export…
Any one have any ideas??
Here is what I am doing to wrap the variables
‘”‘.$csv_output .= $data[0] . ‘”, ‘;
‘”‘.$csv_output .= $data[1] . ‘”, ‘;
‘”‘.$csv_output .= $data[2] . ‘”, ‘;
‘”‘.$csv_output .= $data[3] . ‘”, ‘;
‘”‘.$csv_output .= $data[4] . ‘”, ‘;
‘”‘.$csv_output .= $data[5] . ‘”, ‘;
‘”‘.$csv_output .= $data[6] . ‘”, ‘;
‘”‘.$csv_output .= $data[7] . ‘”, ‘;
If I echo the $csv_output, it shows properly but exports nothing. Removing the quotes fixes the issue but because I have commas in some of my fields the columns do not line up right
Ignore the code, I typed it wrong
this is what I am using
$csv_output .= ‘”‘. $data[0] . ‘”, ‘;
$csv_output .= ‘”‘. $data[1] . ‘”, ‘;
etc.
all the other text is relevant
Thanks a lot you help me a lot.
Nice script but i have one question. Can you help me with special chars?
I have a column “name” and sometimes i have a name with ë or something like that in it. So i used the html_entity_decode($row[‘name’]; but it doesn’t work for me. Is there any other solution for that?
Well, what do you want the output to be? Do you want the special characters to end up in your CSV file or do you want to substitute them? Or is the script breaking when one of those characters is encountered?
From what it sounds like you may want to add a UTF-8 charset declaration to the PHP script to handle latin language based characters. I’d try that first.
Cheers,
David
There is no problem that the script is breaking with one of those characters but they can’t export them. I have p.e. the name ‘Noël’ and in the csv output file i have Noël. How can i fix that? I used htmlentities for saving the record in the mysql database and with the html_entitiy_decode function i have the correct version in my cms, but not in the csv.
The script is not breaking with one of those characters but the export isn’t good. The name field is saved to the database with the htmlentities and in the cms i used the html_entity_decode function for the output and that’s working well. I the csv file it isn’t working with the html_entity_decode function. I used the in the beginning of my page.
Brecht,
Got it…you’re having a characterset issue. Put the following before your mysql_query command and that should take care of it.
mysql_set_charset(‘utf8’);
I’ll be updating the files shortly. Found all kinds of sloppy errors anyhow and fixed them.
David
Whoops, also forgot you need to add these to export.php when creating the csv file. So the CSV file is also UTF8 encoded.
header(“Content-type: application/vnd.ms-excel”);
header(“Content-Encoding: UTF-8”);
header(“Content-type: text/csv; charset=UTF-8”);
header(“Content-disposition: csv” . date(“Y-m-d”) . “.csv”);
header(“Content-disposition: filename=”.$filename.”.csv”);
echo “xEFxBBxBF”; // UTF-8 BOM
cheers,
David
David,
That’s not working. Now the cms output and the csv output file having the same problem. Therefore the cms was correct with the htmlentities for saving into the database and html
_entity_decode for the output. Or should i leave those functions?
Our should i set the mysql_set_charset(‘utf8?); line before i’m saving it to the database? Now i set it before the query for the ouput.
You could try that…let me ask you this, with the latest changes to both files I posted, are you seeing the characters correctly when you load table.php in your browser?
Changes made to table.php:
$database=”test”;
mysql_connect(“localhost”,”root”,””);
mysql_select_db(“test”);
mysql_set_charset(‘utf8’);
mysql_query(‘SET NAMES UTF-8’);
Changes made to export.php:
header(“Content-type: application/vnd.ms-excel”);
header(“Content-Encoding: UTF-8”); //Added to deal with UTF character support
header(“Content-type: text/csv; charset=UTF-8”);
header(“Content-disposition: csv” . date(“Y-m-d”) . “.csv”);
header(“Content-disposition: filename=”.$filename.”.csv”);
echo “xEFxBBxBF”; // UTF-8 BOM added for UTF character support !IMPORTANT
When I change some characters on my test system to umlauts in the database, I am able to get those to the csv file now (only with the new changes – the UTF-8 BOM is critical). Let me know if you’re still having problems.
Cheers,
It’s not good. I made a insert.php file so i was able to put records in the database in your orders table.
I used the name Joël and the output in the table.php is Joël, exactly the same as in the csv-file.
It sounds like you have a charset mismatch somewhere. Check all your pages for a meta tag declaring the charset. I bet you have some page somewhere that is declaring charset=”iso-8859-1″. You’ll need to change that to .
Cheers,
David
That’s the header of my page.
Sorry Brecht,
My website doesn’t deal with stuff enclosed in greater than and less than signs. Just reply without those symbols.
Great script!!! I change separator ‘, ‘ for ‘;’ (no spaces) and Excel automatically places every data in a new cell. Also i change the header in the same way and works too. Thanks, David.
the export.php linl leads to 404 error.. Please could you make the link active or post a new link for the file
Hi David
Great job, Your script is really useful!
Unfortunately my provider does not support sql.
Would be possible to remove the part of the code on the conversion of data from sql to html, then simply to convert an HTML table to CSV
Thanks in advance!
Martin
Hi Martin,
Thanks. I don’t see why you’d need an HTML table to CSV converter. You can simply copy an HTML table and paste it into Excel and save it as a CSV file. If you needed to do it, you’re going to want to look for an HTML parser that is capable of looking for TR and TD elements like this:
foreach($html->find(‘tr’) as $element)
{
$td = array();
foreach( $element->find(‘th’) as $row)
{
$td [] = $row->plaintext;
}
fputcsv($fp, $td);
$td = array();
foreach( $element->find(‘td’) as $row)
{
$td [] = $row->plaintext;
}
fputcsv($fp, $td);
}
Hope it helps,
David
before this code we have to write $html = new DOMDocument ..??
Excellent! Though I still had to fight a bit because the commas didn’t force it to jump cell.
For anyone else having this issue: it works with ; instead of ,
By the way, is it possible to center-align the content of the cells?
I was having problems with latin characters not displaying correctly in the exported .csv. Finally was able to fix the issue changing a line in the the export.php file.
Replace the final print, from:
print $out;
to:
print utf8_decode($out);
And that should do the trick 😉
Thanks for posting. Hope it helps someone else.
Cheers,
david
[…] https://davidvielmetter.com/tricks/howto-convert-an-html-table-to-csv-using-php/ […]
I had to replace <? to <?php in table.php in order to get this to work in case anyone else is trying this.
or
1. go to php.ini file
2. find, ‘short_open_tag’ and set it to on,
3. restart the server
and the <? tag will work.
thank you very much for publishing this script …
its an extremely wonderful deed …
now , i am gonna spend the next few weeks trying to figure out how
to get … dropdown header sorting filtering like in excel for tables in php !