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.
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).
$csv_output .= $row['orders_id'] . ", "; $csv_output .= $row['value'] . "\n";
To jump to a new cell I append a comma (,) and to jump to a new row I append a newline character (\n).
[Step 4] It took me a while to figure out that I couldn’t successfully export my table from the table.php file directly because table.php has an existing header defined (actually in the application I was working on the header was injected and thus even harder to find) and a new header cannot be re-declared.
<head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Orders Table</title> </head>
So, to get around this I created the export.php file without a header. This file doesn’t need to be displayed, so omitting the header is not a big deal. All this file will do is generate my CSV output and print it to a CSV file with the appropriate CSV file header.
[Step 5] The only thing left to do now was 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.
<form name="export" action="export.php" method="post"> <input type="submit" value="Export table to CSV"> <input type="hidden" value="<? echo $csv_hdr; ?>" name="csv_hdr"> <input type="hidden" value="<? echo $csv_output; ?>" name="csv_output"> </form>
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
/*
This file will generate our CSV table. There is nothing to display on this page,
it is simply used to generate our CSV file 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 generate an output variable called out. It'll have all of our text
for the CSV file.
*/
$out = '';
/*Next we'll check to see if our variables posted and if they did we'll simply
append them to out.
*/
if (isset($_POST['csv_hdr'])) {
$out .= $_POST['csv_hdr'];
$out .= "\n";
}
if (isset($_POST['csv_output'])) {
$out .= $_POST['csv_output'];
}
/*Now we're ready to create a file. This method generates a filename based on
the current date & time.
*/
$filename = $file."_".date("Y-m-d_H-i",time());
//Generate the CSV file header
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
//Print the contents of out to the generated file.
print $out;
//Exit the script
exit;
?>
That’s it folks. That’s how to export an HTML table to CSV in 5 easy to understand steps.
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
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
.