Creating CSV view from CouchDB

Question

I know this should be easy, but I just can't work out how to do it despite having spent several hours looking at it today. There doesn't appear to be a straightforward example or tutorial online as far as I can tell.

I've got several "tables" of documents in a CouchDB database, with each "table" having a different value in a "schema" field in the document. All documents with the same schema contain an identical set of fields. All I want to do is be able to view the different "tables" in CSV format, and I don't want to have to specify the list of fieldnames in each schema.

The CSV output is going to be consumed by an R script, so I don't want any additional headers in the output if I can avoid them; just the list of fieldnames, comma separated, with the values in CSV format.

For example, two records in the "table1" format might look like:

{    
   "schema": "table1",
   "field1": 17,
   "field2": "abc",
   ...
   "fieldN": "abc",
   "timestamp": "2012-03-30T18:00:00Z"
}

and

{
   "schema": "table1",
   "field1": 193,
   "field2": "xyz",
   ...
   "fieldN": "ijk",
   "timestamp": "2012-03-30T19:01:00Z"
}

My view is pretty simple:

"all": "function(doc) {
   if (doc.schema == "table1") {
      emit(doc.timestamp, doc)
   }
 }"

as I want to sort my records in timestamp order.

Presumably the list function will be something like:

"csv": "function(head, req) {
   var row;
   ...
   // Something here to iterate through the list of fieldnames and print them
   // comma separated
   for (row in getRow) {
      // Something here to iterate through each row and print the field values
      // comma separated
   }
}"

but I just can't get my head around the rest of it.

If I want to get CSV output looking like

"timestamp", "field1", "field2", ..., "fieldN"
"2012-03-30T18:00:00Z", 17, "abc", ..., "abc"
"2012-03-30T19:01:00Z", 193, "xyz", ..., "ijk"

what should my CouchDB list function look like?

Thanks in advance

Answer 1

The list function that works with your given map should look something like this:

function(head,req) {
  var headers;
  start({'headers':{'Content-Type' : 'text/csv; charset=utf-8; header=present'}});
  while(r = getRow()) {
    if(!headers) {
      headers = Object.keys(r.value);
      send('"' + headers.join('","') + '"\n');
    }
    headers.forEach(function(v,i) {
      send(String(r.value[v]).replace(/\"/g,'""').replace(/^|$/g,'"'));
      (i + 1 < headers.length) ? send(',') : send('\n');
    });
  }
}

Unlike Ryan's suggestion, the fields to include in the list are not configurable in this function, and any changes in order or included fields would have to be written in. You would also have to rewrite any quoting logic needed.

Answer 2

Here some generic code that Max Ogden has written. While it is in node-couchapp form, you probably can get the idea:

var couchapp = require('couchapp')
  , path = require('path')
  ;

ddoc = { _id:'_design/csvexport' };

ddoc.views = {
  headers: {
    map: function(doc) {
      var keys = [];
      for (var key in doc) {
        emit(key, 1);        
      }
    },
    reduce: "_sum"
  }
};

ddoc.lists = {
  /**
   * Generates a CSV from all the rows in the view.
   *
   * Takes in a url encoded array of headers as an argument. You can
   * generate this by querying /_list/urlencode/headers. Pass it in
   * as the headers get parameter, e.g.: ?headers=%5B%22_id%22%2C%22_rev%5D
   *
   * @author Max Ogden
   */
  csv: function(head, req) {  
    if ('headers' in req.query) {
      var headers = JSON.parse(unescape(req.query.headers));

      var row, sep = '\n', headerSent = false, startedOutput = false;

      start({"headers":{"Content-Type" : "text/csv; charset=utf-8"}});
      send('"' + headers.join('","') + '"\n');
      while (row = getRow()) {
        for (var header in headers) {
          if (row.value[headers[header]]) {
            if (startedOutput) send(",");
            var value = row.value[headers[header]];
            if (typeof(value) == "object") value = JSON.stringify(value);
            if (typeof(value) == "string") value = value.replace(/\"/g, '""');
            send("\"" + value + "\"");
          } else {
            if (startedOutput) send(",");
          } 
          startedOutput = true;
        }
        startedOutput = false;
        send('\n');
      }
    } else {
      send("You must pass in the urlencoded headers you wish to build the CSV from. Query /_list/urlencode/headers?group=true");
    }
  }
}

module.exports = ddoc;

Source: https://github.com/kanso/kanso/issues/336