Simple CSV Export with jQuery & PHP

ulrike

Hello,

after trying out TableTools (despite hating Flash) which didn't really work for me, I wrote a simple and fast CSV export function in jQuery which I wanted to share here. Hope this may be helpful to some of you. I am sure one can still do this better than I did, please do not hesitate to comment :)

Here ist the function :
[code]
function table2csv(oTable, exportmode, tableElm) {
var csv = '';
var headers = [];
var rows = [];

// Get header names
$(tableElm+' thead').find('th').each(function() {
var $th = $(this);
var text = $th.text();
var header = '"' + text + '"';
// headers.push(header); // original code
if(text != "") headers.push(header); // actually datatables seems to copy my original headers so there ist an amount of TH cells which are empty
});
csv += headers.join(',') + "\n";

// get table data
if (exportmode == "full") { // total data
var total = oTable.fnSettings().fnRecordsTotal()
for(i = 0; i < total; i++) {
var row = oTable.fnGetData(i);
row = strip_tags(row);
rows.push(row);
}
} else { // visible rows only
$(tableElm+' tbody tr:visible').each(function(index) {
var row = oTable.fnGetData(this);
row = strip_tags(row);
rows.push(row);
})
}
csv += rows.join("\n");

// if a csv div is already open, delete it
if($('.csv-data').length) $('.csv-data').remove();
// open a div with a download link
$('body').append(''+csv+'');

}

function strip_tags(html) {
var tmp = document.createElement("div");
tmp.innerHTML = html;
return tmp.textContent||tmp.innerText;
}

[/code]

This creates a textarea with the code to be copy pasted manually and a button to download a csv-file.
My table is a table with the class "display". So here are my two export buttons :

[code]
// export only what is visible right now (filters & paginationapplied)
$('#export_visible').click(function(event) {
event.preventDefault();
table2csv(oTable, 'visible', 'table.display');
})

// export all table data
$('#export_all').click(function(event) {
event.preventDefault();
table2csv(oTable, 'full', 'table.display');
})
[/code]
I call these in the same init file where I also set up the dataTable (otherwise I wouldn't have access to the variable).
This is the way I set up my datatable :
[code]
$(document).ready(function(){
var asInitVals = new Array();
var oTable = $('table.display').dataTable();
});
[/code]

And the PHP file csv.php at the website root to generate a downloadable CSV :
[code]
<?
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="tabledata.csv"');
if (isset($_POST['csv'])) {
$csv = $_POST['csv'];
echo $csv;
}
?>
[/code]

The export works also with FixedColumns.

Thank you Allan for your great work and documentation.

Best regards.

razibul

dear Ulrike,
i copied your code and apply to my project. but it shows error in csv.php file. i put the file in root folder.
" The HTTP verb POST used to access path '/csv.php' is not allowed. " this is error. what should i do now?

ulrike

Hello,

sorry for the late answer. What i understand from your error, this is a permissions problem.
Check http://stackoverflow.com/questions/2209324/the-http-verb-post-used-to-access-path-test-html-is-not-allowed

allan

> despite hating Flash

Hah! You are not alone! But until there is an API for copy to clipboard and saving local files in Javascript, we are a bit suck with it for a client-side processing solution. For all I love the new APIs such as full screen etc, I am surprised that these two don't have a place in HTML5... ;-(

Like your solution :-). Server-side file creation isn't something I wanted to do with the default TableTools since every server is different, but it is certainly useful. Also worth noting that there are a couple of plug-ins for TableTools that can help (specifically the two download plug-ins):
http://datatables.net/extras/tabletools/plug-ins

Allan

csfire

HI
first of all I'm totally new to this javascripts and php, but i was able to integrate datatable into my page.
now i want to export the whole datatable to csv. I'm currently working on simple sql query as a test page. once this page is working I'm planning to go to much complex queries.

so its easier for me to learn to export whole datatables data instead of using anyother method.

This is how i used your script... my datatable is showing as usual that means its connecting with database properly. but there's no button to export csv.

Please help me with this.

I'm using jquery datatables 1.8.2

below is my codes... please help me if I've done something wrong.

[code]
function table2csv(oTable, exportmode, tableElm) {
var csv = '';
var headers = [];
var rows = [];

// Get header names
$(tableElm+' thead').find('th').each(function() {
var $th = $(this);
var text = $th.text();
var header = '"' + text + '"';
// headers.push(header); // original code
if(text != "") headers.push(header); // actually datatables seems to copy my original headers so there ist an amount of TH cells which are empty
});
csv += headers.join(',') + "\n";

// get table data
if (exportmode == "full") { // total data
var total = oTable.fnSettings().fnRecordsTotal();
for(i = 0; i < total; i++) {
var row = oTable.fnGetData(i);
row = strip_tags(row);
rows.push(row);
}
} else { // visible rows only
$(tableElm+' tbody tr:visible').each(function(index) {
var row = oTable.fnGetData(this);
row = strip_tags(row);
rows.push(row);
});
}
csv += rows.join("\n");

// if a csv div is already open, delete it
if($('.csv-data').length) $('.csv-data').remove();
// open a div with a download link
$('body').append(''+csv+'');

}

function strip_tags(html) {
var tmp = document.createElement("div");
tmp.innerHTML = html;
return tmp.textContent||tmp.innerText;
}

// export only what is visible right now (filters & paginationapplied)
$('#export_visible').click(function(event) {
event.preventDefault();
table2csv(oTable, 'visible', 'table.display');
});

// export all table data
$('#export_all').click(function(event) {
event.preventDefault();
table2csv(oTable, 'full', 'table.display');
});

$(document).ready(function(){
var asInitVals = new Array();
var oTable = $('table.display').dataTable();
});








User ID
user
Full Name
User group
<!--World % Users-->



<?php
while ($row = mysql_fetch_array($result)) {
?>

<?=$row['user_id']?>
<?=$row['user']?>
<?=$row['full_name']?>
<?=$row['user_group']?>
<!--<?=$row['world_users']?>-->

<?php
}
?>




[/code]

csfire

Hi guys please do need help with this.

dasapa

Hi,

i can export full data but i need export filtered data (it can be filtered and not visible) and use datatable multi.column filter.
http://www.datatables.net/release-datatables/examples/api/multi_filter.html

I dont know how can obtain value each filter. I think you obtain with function_strip_tags but i dont know how works, what is "div"?

[code] function strip_tags(html) {
var tmp = document.createElement("div");
tmp.innerHTML = html;
return tmp.textContent||tmp.innerText;
}[/code]

latheesan

Hi All,

I was looking for a way to export any given datatable to CSV and came across this post. Thanks ulrike for this. I used your idea and optimised it. Here's what I got so far (working):

[ index.php ]

[code]


CSV Export - DataTable Test
@import "css/demo_table.css";

* { font-family: Verdana; font-size: 12px; }





// When Page Loads
var oTable;
$(document).ready(function()
{
// Init DataTable
oTable = $('#myDataTable').dataTable();

// Handle Btn Clicks
$('.ExportBtn').click(function() {
CSVExportDataTable(oTable, $(this).val());
});
});

// CSV Export Function
function CSVExportDataTable(oTable, exportMode)
{
// Init
var csv = '';
var headers = [];
var rows = [];
var dataSeparator = '|~|';

// Get table header names
$(oTable).find('thead th').each(function() {
var text = $(this).text();
if(text != "") headers.push(text);
});
csv += headers.join(dataSeparator) + "\r\n";

// Get table body data
if (exportMode == 'Full') {
var totalRows = oTable.fnSettings().fnRecordsTotal();
for (var i = 0; i < totalRows; i++) {
var row = oTable.fnGetData(i);
rows.push(row.join(dataSeparator));
}
} else {
$(oTable._('tr:visible', { })).each(function(index, row) {
rows.push(row.join(dataSeparator));
});
}
csv += rows.join("\r\n");

// Proceed if csv data was loaded
if (csv.length > 0)
{
// Ajax Post CSV Data
$.ajax({
type: "POST",
url: 'dt_csv_export.php',
data: {
action: "generate",
csv_type: oTable.attr('id'),
csv_data: csv
},
success: function(download_link) {
location.href = download_link;
}
});
}
}








Rendering engine
Browser
Platform(s)
Engine version
CSS grade



<!-- Table Rows Snipped -->






Export :


[/code]

[ dt_csv_export.php ]

[code]<?php

// Mini-Config
$data_separator = '|~|';

// Handle Generate CSV Action
if (isset($_POST['action']) && $_POST['action'] == 'generate' &&
isset($_POST['csv_type']) && !empty($_POST['csv_type']) &&
isset($_POST['csv_data']) && !empty($_POST['csv_data']))
{
// Delete Temp CSV Files - Older Than 1 Day
$temp_csv_files = glob('*.csv');
if (is_array($temp_csv_files) && sizeof($temp_csv_files)) {
foreach ($temp_csv_files as $temp_csv_file) {
if (is_file($temp_csv_file) && time() - filemtime($temp_csv_file) >= 24*60*60) { // 1 Day Old
unlink($temp_csv_file);
}
}
}

// Write CSV To Disk
$csv_data = explode("\r\n", $_POST['csv_data']);
$csv_file_name = uniqid($_POST['csv_type'] .'_', true) .'.csv';
$h = @fopen($csv_file_name, 'w');
if (false !== $h) {
if (sizeof($csv_data)) {
foreach ($csv_data as $csv_row) {
$csv_row = explode($data_separator, $csv_row);
array_walk_recursive($csv_row, function(&$item) {
$item = preg_replace('/\s+/', ' ', preg_replace('/[\r\n\t]*/', '', $item));
$item = trim(strip_tags($item));
});
fputcsv($h, $csv_row, ',', '"');
}
}
fclose($h);
}
echo file_exists($csv_file_name) ? $csv_file_name : '';
}

// End
exit();

?>[/code]

Hope someone finds this useful.

Regards,
Latheesan