Exporting data from a YUI DataTable

Question

What's the easiest/fastest way to grab data out of a YUI DataTable and turn it into a single CSV or TSV string? I basically just want to implement a one-click way to get the entire DataTable (it should preserve the currently-applied sorting) into a form that users can paste into a spreadsheet.

My DataTable can get pretty big - 5000 to 10000 rows, 5 to 10 columns - so efficiency does matter.

Gavin Brock · Accepted Answer · 2010-03-19T16:40:08.870

6

How about something like this:

function dataTableAsCSV (myDataTable) {

    var i, j, oData, newWin = window.open(),
        aRecs = myDataTable.getRecordSet().getRecords(),
        aCols = myDataTable.getColumnSet().keys;

    newWin.document.write("<pre>");

    for (i=0; i<aRecs.length; i++) {
        oData = aRecs[i].getData();

        for (j=0; j<aCols.length; j++) {
            newWin.document.write( oData[aCols[j].key] + "\t");

        }
        newWin.document.write("\n");

    }

    newWin.document.write("</pre>n");
    newWin.document.close();
}

It will render the data table content as TSV into a new window. It doesn't handle data with tabs in it, but that would just be some extra substitutions on oData[aCols[j].key].

edited Mar 19 '10 at 16:40

answered Mar 19 '10 at 12:42

Gavin Brock

5,027
1
30
33

You know, I'm surprised at how quick that is. – Matt Ball Mar 19 '10 at 13:54
...And it preserves sorting. Awesome! – Matt Ball Mar 19 '10 at 14:01
1

Any idea how then to make the popup open a save/download dialog? Probably requires changing the content type of the popup document or so, which I realize it may not be possible, but if you have a way to do it, that'll be just great. – Meligy Nov 01 '10 at 06:05
Very helpful. I looked into the changing the content type but it's not possible client side. You can call document.open(mimetype) but that won't work since browsers only implement a HTMLDocument as window.document. – Florian Nov 23 '11 at 10:35
About having a save/download dialog, you can create a server side script which echoes the contents passed into it as an argument. Make sure you set the correct MIME type for the response. =) – bhagyas Sep 07 '12 at 10:27

George Campbell · Answer 2 · 2013-09-14T15:30:09.050

The above answer works great for YUI up to version 3.4. However, the data-table was refactored beginning with version 3.5. My converter encloses cell values in double quotes, escapes double quotes in cell values and handles one level of column nesting, if it exists.

Here is a fiddle that demonstrates my converter: http://jsfiddle.net/geocolumbus/AFB3h/3/

// Function to convert a DataTable with zero or one nested columns to CSV
function convertToCSV(myDataTable) {
    var col,
    colIndex = 0,
        colKey,
        rowString,
        ret,
        cell,
        headerString = "";

    while (col = myDataTable.getColumn(colIndex++)) {
        if (col.children == null) {
            headerString += '"' + col.key + '",';
        } else {
            Y.Array.each(col.children, function (child) {
                headerString += '"' + child.key + '",';
            });
        }
    }
    ret = headerString.replace(/,$/, '\n');

    Y.Array.each(myDataTable.data.toJSON(), function (item) {
        colIndex = 0;
        rowString = "";
        while (col = myDataTable.getColumn(colIndex++)) {
            if (col.children == null) {
                cell = item[col.key].replace(/"/g, "\\\"");
                rowString += '"' + cell + '",';
            } else {
                Y.Array.each(col.children, function (child) {
                    cell = item[child.key].replace(/"/g, "\\\"");
                    rowString += '"' + cell + '",';
                });
            }
        }
        ret += rowString.replace(/,$/, '') + "\n";
    });

    return ret;
}

Exporting data from a YUI DataTable

2 Answers2