Robomongo: Export to csv

[akekrakbar]

It would be nice to be able to export documents from robomongo to .csv files.

[jdarling]

Would love CSV export, or the option to run JS on the result set and in the raw view be able to save it to a text file. That way we could just use an off the shelf CSV converter or whatever.

[schetnikovich]

Hi @akekrakbar, @jdarling,

This feature would be very nice. We added this feature request to our backlog.

or the option to run JS on the result set and in the raw view be able to save it to a text file

This is a nice proposal! We really should consider adding API for working with local filesystem right from Robomongo shell. Thanks!

[jdarling]

Glad to hear I had a good idea LOL. Wish I had the experience with Java and the time to help implement it. Unfortunately I really don't have any skills with the exception of being able to read Java to a minor extend :)

[joegoggins]

+1

CSV export would be spectacular!

Great tool, thanks for building it!

[Robdr]

+1 for me as well.

[jeff-phil]

Put this into your .robomongorc.js and then call within RoboMongo shell:

db.mycollection.find().toCSV();

or to change the deliminator to pipe, and single quote pass in two params:

db.mycollection.find().toCSV('|','\'');

DBQuery.prototype.toCSV = function(deliminator, textQualifier) {
var count = -1;
var headers = [];
var data = {};

var cursor = this;

deliminator = deliminator == null ? ',' : deliminator;
textQualifier = textQualifier == null ? '\"' : textQualifier;

while (cursor.hasNext()) {

    var array = new Array(cursor.next());

    count++;

    for (var index in array[0]) {
        if (headers.indexOf(index) == -1) {
            headers.push(index);
        }
    }

    for (var i = 0; i < array.length; i++) {       
        for (var index in array[i]) {
            data[count + '_' + index] = array[i][index];
        }
    }
}

var line = '';

for (var index in headers) {
    line += headers[index] + ',';
}

line = line.slice(0, -1);
print(line);

for (var i = 0; i < count + 1; i++) {

    var line = '';
    var cell = '';
    for (var j = 0; j < headers.length; j++) {
        cell = data[i + '_' + headers[j]];
        if (cell == undefined) cell = '';
        line +=  textQualifier + cell + textQualifier + deliminator;
    }

    line = line.slice(0, -1);
    print(line);
}

}

[jdarling]

@jeff-phil where is the .robomongorc.js file located? I searched the install directory and around the typical locations on Windows but I don't see that file anyplace with version 8.4RC2

[stmoroz]

@jdarling

.robomongorc.js file doesn't exist by default. You should create it and place to your home directory. Read more here: http://robomongo.org/whats-new-in/robomongo-0.8.1.html

Thanks.

[stou]

@jeff-phil Works as advertised. Thank :-)

+1 for the suggestion of writing the output to the local file system.

[jdarling]

This is a much more advanced version of the above that flattens complex structures in records (IE: nested objects) at the cost of having to place all objects into memory before starting work.

Options:

  options.delim = options.delim || ',';
    // Delimiter between fields defaults to ,
  options.textDelim = options.textDelim || '"';
    // Text delimiter to wrap text in, defaults to "
  options.eoln = options.eoln || '\r\n';
    // Line end character, defaults \r\n
  options.headerDelim = options.headerDelim || '_';
    // Delimiter when joining complex object type headings, defaults _
  options.escapeTextDelim = options.escapeTextDelim || ('\\'+options.textDelim);
    // Escape character for embedded text delimiter defaults to \" or \<textDelim>

Hope it helps

var toCSV = function(data, options){
  var headers = [];
  var records = [];
  var line;
  var response = '';
  var i, l = data.length, rl=0, j, k;
  var reReplaceTextDelim;
  var processRecord = function(src, rec, prefix){
    var key, val, idx;
    prefix = prefix || '';
    rec = rec || new Array(headers.length);
    for(key in src){
      val = src[key];
      key = prefix+key;
      switch(typeof(val)){
        case('object'):
          if(val instanceof Array){
            processRecord(val, rec, key+options.headerDelim);
            break;
          }else if(val instanceof ObjectId){
            if(!options.includeObjectIdWrapper){
              val = ''+val;
            }
          }else if(!(val instanceof Date)){
            processRecord(val, rec, key+options.headerDelim);
            break;
          }
        default:
          if((idx = headers.indexOf(key))===-1){
            idx = headers.length;
            headers.push(key);
          }
          rec[idx] = val.toString();
      }
    }

    return rec;
  };

  options = options || {};
  options.delim = options.delim || ',';
  options.textDelim = options.textDelim || '"';
  options.eoln = options.eoln || '\r\n';
  options.headerDelim = options.headerDelim || '_';
  options.escapeTextDelim = options.escapeTextDelim || ('\\'+options.textDelim);
  reReplaceTextDelim = new RegExp(options.textDelim, 'gi');

  if(options.includeHeaders === void(0)){
    options.includeHeaders = true;
  }

  for(i=0; i<l; i++){
    line = processRecord(data[i]);
    if(rl < line.length){
      rl = line.length;
    }
    records.push(line);
  }
  if(options.includeHeaders){
    response = options.textDelim + headers.join(options.textDelim + options.delim + options.textDelim) + options.textDelim + options.eoln;
  }
  for(i=0; i<l; i++){
    line = records[i];
    if(line.length<rl){
      line = line.concat(new Array(rl-line.length));
    }
    k = line.length;
    for(j=0; j<k; j++){
      if(line[j] !== void(0)){
        response += options.textDelim + line[j].replace(reReplaceTextDelim, options.escapeTextDelim) + options.textDelim;
      }
      if(j<k-1){
        response += options.delim;
      }
    }
    response += options.eoln;
  }

  return response;
};

DBQuery.prototype.toCSV = function(options){
  var cursor = this;
  var data = [];
  while(cursor.hasNext()){
    data.push(cursor.next());
  }
  print(toCSV(data, options));
};

[FJLopezGarcia]

Hi all,
I am new in Mongo DB and i need to export to CSV, could you please help me on this? what i am doing wrong?

I have added into my .robomongorc.js the lines provided in this post, but when i go to my robomongo and click on a Collection (Example "citties") and I run db.cities.toCSV() I am receiving the folloing issue: "TypeError: cursor.hasNext is not a function C:/Users/Javi/.robomongorc.js:84"

Please notice that I have replace the line:
DBQuery.prototype.toCSV = function(options){ and
and I am using:
DBCollection.prototype.toCSV = function(options){

Here you are the code i have added into my .robomongorc.js

var toCSV = function(data, options){
var headers = [];
var records = [];
var line;
var response = '';
var i, l = data.length, rl=0, j, k;
var reReplaceTextDelim;
var processRecord = function(src, rec, prefix){
var key, val, idx;
prefix = prefix || '';
rec = rec || new Array(headers.length);
for(key in src){
val = src[key];
key = prefix+key;
switch(typeof(val)){
case('object'):
if(val instanceof Array){
processRecord(val, rec, key+options.headerDelim);
break;
}else if(val instanceof ObjectId){
if(!options.includeObjectIdWrapper){
val = ''+val;
}
}else if(!(val instanceof Date)){
processRecord(val, rec, key+options.headerDelim);
break;
}
default:
if((idx = headers.indexOf(key))===-1){
idx = headers.length;
headers.push(key);
}
rec[idx] = val.toString();
}
}

return rec;

};

options = options || {};
options.delim = options.delim || ','; // Delimiter between fields defaults to ,
options.textDelim = options.textDelim || '"'; // Text delimiter to wrap text in, defaults to "
options.eoln = options.eoln || '\r\n'; // Line end character, defaults \r\n
options.headerDelim = options.headerDelim || '_'; // Delimiter when joining complex object type headings, defaults _
options.escapeTextDelim = options.escapeTextDelim || ('\'+options.textDelim); // Escape character for embedded text delimiter defaults to \" or
reReplaceTextDelim = new RegExp(options.textDelim, 'gi');

if(options.includeHeaders === void(0)){
options.includeHeaders = true;
}

for(i=0; i line = processRecord(data[i]);
if(rl < line.length){
rl = line.length;
}
records.push(line);
}
if(options.includeHeaders){
response = options.textDelim + headers.join(options.textDelim + options.delim + options.textDelim) + options.textDelim + options.eoln;
}
for(i=0; i line = records[i];
if(line.length line = line.concat(new Array(rl-line.length));
}
k = line.length;
for(j=0; j if(line[j] !== void(0)){
response += options.textDelim + line[j].replace(reReplaceTextDelim, options.escapeTextDelim) + options.textDelim;
}
if(j response += options.delim;
}
}
response += options.eoln;
}
return response;
};

DBCollection.prototype.toCSV = function(options){
var cursor = this;
var data = [];
while(cursor.hasNext()){
data.push(cursor.next());
}
print(toCSV(data, options));
};

[stennie]

@FJLopezGarcia Since Robomongo does not (yet) have a feature to export to CSV, I would instead recommend using the standard mongoimport command line utility that is included with MongoDB.

[jdarling]

@FJLopezGarcia you can't do that against a collection, you have to have a query to have the paged results. Instead change the code back and use db.mycollection.find().toCSV()

That or, you will have to make the collection version do a query before it does the toCSV action:

DBCollection.prototype.toCSV = function(options){
  var cursor = this.find(); // Notice using the .find() on this
  var data = [];
  while(cursor.hasNext()){
    data.push(cursor.next());
  }
  print(toCSV(data, options));
};

[FJLopezGarcia]

Thank you for you quick response, it runs perfectly.

[FJLopezGarcia]

Is it posible to export the results to a CSV file directly?

[asuraphel]

@jeff-phil Thanks a lot for the script!

[bkurkela]

@jdarling Thank for you the script above, it works like a charm. I do have one question though - one of the fields in my output is a datetime value, and it's currently displaying in the output as: "Mon Jun 02 2014 10:48:15 GMT-0400 (Eastern Daylight Time)".

Is there a way I can edit your script to change the format out the output if the field is a datetime type?

[mderazon]

Worked great :+1:

[satblip]

thanks to @jeff-phil and @jdarling these two patches are just awesome!!

[ahaedike]

To get a quick dump in CSV format. Copy/paste the output into a file with .csv extension:

db.mycollection.find().forEach(function(thing){
print(thing.name+","+thing.email);
});

[akostadinov]

Export result to some file format is really important. Otherwise using the command line client remains necessary.

[DavidGagne]

Works fantastic. Thanks!

+1 for the request to sensibly format dates

[successamp]

+1 worked. Thank you!

[psx-nb]

Would also really appreciate: Export result to some file format is really important.

[ssureshb1]

this is excellent script

[SaintPeter]

@ahaedike Your Quick 'n Dirty solution was just the thing. Thank you!

Seriously, though, this feature request has been open for just over 2 years. This seems like really basic functionality. Has development on RoboMongo stopped?

[jdarling]

My guess, since Mongo 3 support is way behind thus there are open bugs like crazy since you can't use Mongo 3 with RoboMongo, is yes. RoboMongo is dead. Good news, MongoChef (http://3t.io/mongochef/) works well enough. Missing many of the nice things of RoboMongo, but at least it works.

[officert]

@jdarling I'm working on a new open-source Mongo DB management tool. I built it out of frustration with the existing set of Mongo DB management tools. Check it out here: http://mongotron.io
Looking for comments, feedback, contributions, anything!

[officert]

it's still very early stage and missing many of the features of Robomongo and Mongochef, but it's clean and built on modern tech.

[jdarling]

@officert I'll take a look

[phatpham9]

+1

[reneej1729]

This works great for me when I run it with .find(), but I would like to use this with an aggregate function. I get an error that says

TypeError: db.getCollection("uploadFieldMapping").aggregate([{
    $project:{_id:"$_id", columnName:"$columnName", userChoice:"$userChoice", psMatched:{$cond:[{$eq:["$userChoice", "$psSuggestion"]}, 1, 0]}}}, 
    {$group:{_id:{columnName:"$columnName", userChoice:"$userChoice"}, count:{$sum:1}, psMatched:{$sum:"$psMatched"}}}]).toCSV 
    is not a function (shell):19

The query works fine on its own. How might I get the results of the query to a CSV?

[prasgema]

+1

[nikhilkulkarni]

+1 to above

[fguillen]

+1

There is a SO thread: http://stackoverflow.com/questions/28733692/how-to-export-json-from-mongodb-using-robomongo

[Perados]

+1

[simsekgokhan]

Thanks All for your interest and very sorry for long delay.
We have officially started development for this feature. (Export/Import CSV/JSON)
Planned to be included not in the next release (Robomongo 0.9.0) but in the following release (Robomongo 1.0) with Replica Set support.

Regards from Robomongo Team

Follow the latest releases and news here:
https://twitter.com/Robomongo

[jonfairbanks]

It looks like version 1.0 RC1 has come out, but I do not see support for exporting in the changelog.

1.0 RC1 Changelog: http://blog.robomongo.org/robomongo-1-rc1/

Can you provide an update on this feature request? Is this still on track for release?

Thanks!

[simsekgokhan]

Hi @jonfairbanks , thanks a lot for your interest. We have already started and almost completed the development of export. We will continue with import. We are now reviewing priorities of critical/high-vote features, export/import is still one of the most important candidates for next releases.
Best Regards.

[jonfairbanks]

Thanks for the update, @simsekgokhan. Eagerly awaiting the next version to test this new feature. 😄

[simsekgokhan]

Documenting @jeff-phil's working solution https://github.com/Studio3T/robomongo/issues/348#issuecomment-29362405 into Robomongo Wiki:
https://github.com/Studio3T/robomongo/wiki/How-to-export-to-CSV

Thanks a lot @jeff-phil

[jonfairbanks]

@simsekgokhan: It looks like the final version of 1.0 has dropped, but I do not see support for CSV exports mentioned in either the changelog or within the app. Unfortunately the solution shared above does not work in all use-cases so I'm pretty eager to test the proposed in-app option.

Is this feature still on track? I see it is no longer mentioned in the blog's roadmap so hoping it didn't get the axe.

Thanks again for any updates you may have.

[simsekgokhan]

@jonfairbanks
Export/import is definitely on track. There is a high chance of finishing and including this feature in Robomongo 1.2 which will be planned soon after we release Robomongo 1.1 release with MongoDB 3.4 suport in a week. (We will also add this plan also into our new blogs, thanks for reminding that)

Please see section 'Plans for Future:' on whats-planned-for-the-next-releases

[malfter]

+1

[xdolnak]

+1

[rustanacexd]

+1

[jonfairbanks]

For those of you still waiting for Export support in Robomongo, checkout Studio3T. The interface is not quite as user friendly when compared to Robomongo, but it does support import/export in several formats including CSV and JSON.

https://studio3t.com/download/

[iclear]

printjsononeline function is very useful.
db.getCollection('autos').aggregate([{$group : { _id : { brand: "$brand", series: "$series" }, count: { $sum: 1 } }}]).forEach(function(x){printjsononeline(x)});

[okonon]

This feature will be a killer!

[hardikmodha]

Slightly different from @ahaedike mentioned, you can use --eval and then use redirect to output into a csv file.

mongo test-db --eval db.mycollection.find().forEach(function(thing{print(thing.name+","+thing.email);}); > output.csv

[iannewson]

Here's how to do it with aggregates:

https://stackoverflow.com/a/48928948/511012

[IGZmanuelMartinVivaldi]

[Solved]
.robomongorc.js should be in:
C:\Users{Username}\

I tried the toCSV() function workaround in Robomongo 1.2.1 but I get this error:

Failed to execute script.
Error: TypeError: db.getCollection(...).find(...).toCSV is not a function : @(shell):1:1

Any idea? Thanks

[iannewson]

It looks like you haven't loaded the js file containing the toCSV function. Make sure you don't get any errors at this step.

[anandsunderraman]

I added another method to convert an array to CSV
https://gist.github.com/anandsunderraman/5f1f49dec3e4cbd4ad64e1b26e548658