How to export a collection to CSV in MongoDB?

Question

How do you export all entries in the MongoDB collection to a .csv file?

 mongoexport --host localhost --db dbname --collection name --type=csv > test.csv 

This requires you to specify the name of the fields that I need to export. Is it possible to simply export all fields without specifying field names?

Answer 1

@ karoly-horvath has this right. Fields are required for csv.

According to this error in the MongoDB problem tracker https://jira.mongodb.org/browse/SERVER-4224 you MUST provide fields when exporting to csv . The documents on it are not clear. This is the cause of the error.

Try the following:

 mongoexport --host localhost --db dbname --collection name --csv --out text.csv --fields firstName,middleName,lastName 

UPDATE:

This commit: https://github.com/mongodb/mongo-tools/commit/586c00ef09c32c77907bd20d722049ed23065398 corrects documents for 3.0.0-rc10 and later. He is changing

 Fields string `long:"fields" short:"f" description:"comma separated list of field names, eg -f name,age"` 

to

 Fields string `long:"fields" short:"f" description:"comma separated list of field names (required for exporting CSV) eg -f \"name,age\" "` 

Answer 2

In addition, you are not allowed spaces between field names separated by commas.

BAD: -f firstname, lastname

GOOD: -f firstname,lastname

Answer 3

 mongoexport --help .... -f [ --fields ] arg comma separated list of field names eg -f name,age --fieldFile arg file with fields names - 1 per line 

You need to manually specify it, and if you think about it, it makes sense. MongoDB is sketchy; CSV, on the other hand, has a fixed layout for columns. Not knowing which fields are used in different documents, it is impossible to dump a CSV.

If you have a fixed schema, perhaps you can get one document, collect the field names from it using a script and pass it to mongoexport.

Answer 4

If you want, you can export all collections to csv without specifying --fields (exports all fields).

From http://drzon.net/export-mongodb-collections-to-csv-without-specifying-fields/ run this bash script

 OIFS=$IFS; IFS=","; # fill in your details here dbname=DBNAME user=USERNAME pass=PASSWORD host=HOSTNAME:PORT # first get all collections in the database collections=`mongo "$host/$dbname" -u $user -p $pass --eval "rs.slaveOk();db.getCollectionNames();"`; collections=`mongo $dbname --eval "rs.slaveOk();db.getCollectionNames();"`; collectionArray=($collections); # for each collection for ((i=0; i<${#collectionArray[@]}; ++i)); do echo 'exporting collection' ${collectionArray[$i]} # get comma separated list of keys. do this by peeking into the first document in the collection and get his set of keys keys=`mongo "$host/$dbname" -u $user -p $pass --eval "rs.slaveOk();var keys = []; for(var key in db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]) { keys.push(key); }; keys;" --quiet`; # now use mongoexport with the set of keys to export the collection to csv mongoexport --host $host -u $user -p $pass -d $dbname -c ${collectionArray[$i]} --fields "$keys" --csv --out $dbname.${collectionArray[$i]}.csv; done IFS=$OIFS; 

Answer 5

I could not get mongoexport to do this for me. I found that in order to get an exhaustive list of all fields, you need to scroll through the entire collection once. Use this to generate headers. Then run the collection again to fill out these headings for each document.

I wrote a script to do this. Convert MongoDB documents to csv regardless of the differences in the schema between individual documents.

https://github.com/surya-shodan/mongoexportcsv

Answer 6

Also, if you want to export internal json fields, use dot (. Operator).

JSON entry:

 { "_id" : "00118685076F2C77", "value" : { "userIds" : [ "u1" ], "deviceId" : "dev" } 

mongoexport command with point operator (using mongo version 3.4.7):

./mongoexport --host localhost --db myDB --collection myColl --type = csv --out out.csv --fields value.deviceId, value.userIds

Csv output:

 value.deviceId,value.userIds d1,"[""u1""]" d2,"[""u2""]" 

Note. Make sure you are not exporting an array. This will damage the CSV format, for example, the field user identifiers shown above