Redirect output of mongo query to a csv file
Extending other answers:
I found @GEverding's answer 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);
}
});
Execute the following command to export results:
mongo test_db < ./test_db.js >> ./test_db.csv
Unfortunately, it adds additional text to the CSV file which requires processing the file before we can use it:
MongoDB shell version: 3.2.10
connecting to: test_db
But we can make mongo shell stop spitting out those comments and only print what we have asked for by passing the --quiet
flag
mongo --quiet test_db < ./test_db.js >> ./test_db.csv
Mongo's in-built export is working fine, unless you want to any data manipulation like format date, covert data types etc.
Following command works as 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
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. First I couldn't get any of the json to csv converters to work (although this one looked promising). What I ended up doing was manually writing the csv file in my mongo script.
This is a simple version but essentially what I did:
print("name,id,email");
db.User.find().forEach(function(user){
print(user.name+","+user._id.valueOf()+","+user.email);
});
This I just piped the query to stdout
mongo test export.js > out.csv
where test
is the name of the database I use.
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 that in a file, say "export.js". Run the following command:
mongo <host>/<dbname> -u <username> -p <password> export.js > out.csv