Redirect mongo request output to csv file

Question

I am using MongoDB 2.2.2 for a 32-bit Windows7 machine. I have a complex aggregation request in a .js file. I need to execute this file in a shell and redirect the output to a CSV file. I guarantee that the request returns "flat" json (without nested keys), so it is inherently converted to neat csv.

I know about load() and eval() . eval() requires me to enclose the entire request in a wrapper and only allow printjson() inside the script, whereas I need csv. And, the second way: load() .. It prints the output on the screen and again in json format.

Is there a way Mongo can do this conversion from json to csv? (I need a csv file to prepare diagrams for the data). I think:

1. Either mongo has a built-in command for this, which I cannot find right now.
2. Mongo cannot do this for me; In most cases, I can send the json file to a file, which I then need to convert to csv. 3. Mongo can send json output to a temporary collection, the contents of which can be easily mongoexported in csv format. But I think that only requests with a decrease in the number of cards support output collections. It is right? I need this to request aggregation.

Thanks for any help :)

Answer 1

I know this question is old, but I spend an hour trying to export a complex query to csv, and I wanted to share my thoughts. Firstly, I couldn't get any of the json-csv converters to work (although this one looked promising). What I ended up with is manually writing the csv file in my mongo script.

This is a simple version, but basically what I did:

 print("name,id,email"); db.User.find().forEach(function(user){ print(user.name+","+user._id.valueOf()+","+user.email); }); 

I just passed the request to stdout

 mongo test export.js > out.csv 

where test is the name of the database used.

Answer 2

Mongo's native export works fine unless you want to manipulate data such as date formatting, hidden data types, etc.

The following command works like a charm.

  mongoexport -h localhost -d databse -c collection --type=csv --fields erpNum,orderId,time,status -q '{"time":{"$gt":1438275600000}, "status":{"$ne" :"Cancelled"}}' --out report.csv 

Answer 3

Here is what you can try:

 print("id,name,startDate") cursor = db.<collection_name>.find(); while (cursor.hasNext()) { jsonObject = cursor.next(); print(jsonObject._id.valueOf() + "," + jsonObject.name + ",\"" + jsonObject.stateDate.toUTCString() +"\"") } 

Save this in a file, say, "export.js". Run the following command:

 mongo <host>/<dbname> -u <username> -p <password> export.js > out.csv 

Answer 4

Take a look at this

to output from the mongo shell to a file. There is no support for csv output from mongos shell. You will need to write javascript yourself or use one of the many converters available. For example, Google "converts json to csv".

Answer 5

Extension of other answers:

I found @GEverding's answer the most flexible. It also works with aggregation:

test_db.js

 print("name,email"); db.users.aggregate([ { $match: {} } ]).forEach(function(user) { print(user.name+","+user.email); } }); 

Run the following command to export the results:

 mongo test_db < ./test_db.js >> ./test_db.csv 

Unfortunately, it adds additional text to the CSV file, which requires processing of the file before we can use it:

 MongoDB shell version: 3.2.10 connecting to: test_db 

But we can make the mongo shell stop splashing out these comments and print only what we requested by passing the --quiet flag

 mongo --quiet test_db < ./test_db.js >> ./test_db.csv