What's it all about, Alfie?

Today I did a little digging on the idea of exporting a Google Visualization Table to CSV all in javascript. Face it - the table is already there… it's got the data… the trick is how to get it all up and going for the CSV export. Well… as it turns out, it's not all that hard. I was pretty surprised.

The core of it is really the Google Visualization DataTable. Since that's the core of most of the Visualizations, that's a great universal starting point. What we're really doing in the code is making a simple javascript method that will make a URI and encode it, such that when it's opened, it'll appear as a download to the browser and be kept as a file.

The first part is to save the DataTable when you render the Google Table on the page:

  // this is the Google DataTable we'll be creating each time
  var dtable = null;
 
  // This method looks at the selected data set and loads that into
  // a new table for the target div and redraws it.
  function render(tbl) {
    // save this data table for later
    dtable = tbl;
    // now create a Google Table and populate it with this data
    var dest = document.getElementById('table_div');
    var table = new google.visualization.Table(dest);
    table.draw(tbl, table_config);
  }

At this point, we have the DataTable, and then we can place the button anywhere on the page, I happened to place it, centered at the bottom of the page:

  <p align="center">
    <input type="button" id="toCSV" value="Click to download data as CSV"
     onclick="toCSV()" />
  </p>

So that when the user clicks on the button the following code will be run:

  // this downloads the current data table as a CSV file to the client
  function toCSV() {
    var data = dtable;
    var csvData = [];
    var tmpArr = [];
    var tmpStr = '';
    for (var i = 0; i < data.getNumberOfColumns(); i++) {
      // replace double-quotes with double-double quotes for CSV compatibility
      tmpStr = data.getColumnLabel(i).replace(/"/g, '""');
      tmpArr.push('"' + tmpStr + '"');
    }
    csvData.push(tmpArr);
    for (var i = 0; i < data.getNumberOfRows(); i++) {
      tmpArr = [];
      for (var j = 0; j < data.getNumberOfColumns(); j++) {
        switch(data.getColumnType(j)) {
          case 'string':
            // replace double-quotes with double-double quotes for CSV compat
            tmpStr = data.getValue(i, j).replace(/"/g, '""');
            tmpArr.push('"' + tmpStr + '"');
            break;
          case 'number':
            tmpArr.push(data.getValue(i, j));
            break;
          case 'boolean':
            tmpArr.push((data.getValue(i, j)) ? 'True' : 'False');
            break;
          case 'date':
            // decide what to do here, as there is no universal date format
            break;
          case 'datetime':
            // decide what to do here, as there is no universal date format
            break;
          case 'timeofday':
            // decide what to do here, as there is no universal date format
            break;
          default:
            // should never trigger
        }
      }
      csvData.push(tmpArr.join(','));
    }
    var output = csvData.join('\n');
    var uri = 'data:application/csv;charset=UTF-8,' + encodeURIComponent(output);
    window.open(uri);
  }

You can see the entire page here:

The downside of this is that the file will have an unusual name. On Mac OS X with Safari 6.0.1, it's "Unknown". On other platforms, I'm sure it's something nearly as odd and useless, but that's the name of the game. There's seemingly no way to get the name of the file in the URI or the window.open() method.

Still… I'm pretty pleased. We're looking at a 100% client-side, javascript solution to the CSV generation problem. That's pretty nice. If you look at the code, there's really very little that's exclusive to the Google DataTable - it's really just the means to get the headers, and the row and column data. We could have easily built this from any regular data source and made that work as well.

Sweet.

This entry was posted on Tuesday, September 25th, 2012 at 8:38 am and is filed under Coding, Cube Life, Open Source Software. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.