How to export collection to CSV in MongoDB?

Question

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

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

This asks me to specify name of the fields I need to export. Can I just export all the fields without specifying the names of fields?

Void

Answer 1

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

According to this bug in the MongoDB issue tracker https://jira.mongodb.org/browse/SERVER-4224 you MUST provide the fields when exporting to a csv. The docs are not clear on it. That is the reason for the error.

Try this:

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 fixes the docs for 3.0.0-rc10 and later. It changes

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

to

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

VERSION 3.0 AND ABOVE:

You should use --type=csv instead of --csv since it has been deprecated.

More details: https://docs.mongodb.com/manual/reference/program/mongoexport/#export-in-csv-format

Full command:

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

Anarthrous

As of version 3.0.6 mongoexport says csv flag is deprecated; please use --type=csv instead – Kanter

Thanks (to anonymous) for the VERSION 3.0 AND ABOVE edit. – Anarthrous

is there a fast way to include all fields instead of naming each? – Weinstock

Answer 2

Also, you are not allowed spaces between comma separated field names.

BAD: -f firstname, lastname

GOOD: -f firstname,lastname

Pembrook

Answer 3

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

You have to manually specify it and if you think about it, it makes perfect sense. MongoDB is schemaless; CSV, on the other hand, has a fixed layout for columns. Without knowing what fields are used in different documents it's impossible to output the CSV dump.

If you have a fixed schema perhaps you could retrieve one document, harvest the field names from it with a script and pass it to mongoexport.

Cosette

I was just searching if I could get the list of fields from the a record. i.e from db.collection.finOne().getFields(). But I guess that is not the right method(getFields). I tried getKeys() as well. Otherwise I will have to get the record with key:value hashes. – Void

I'm trying to do the same thing, but to figure out why it isn't import csv files properly. In my case, I need it to tell me everything about itself, including which fields it "invented" by itself. So in my case it doesn't make perfect sense to have to specify the fields, because I don't know what they all are! – Baram

On the field harvesting script front, I posted this a year or so back, may give you a few ideas. – Buttonhole

Answer 4

If you want, you can export all collections to csv without specifying --fields (will export 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;

Myrtice

Only issue with the above script is that it assumes the first document in each collection contains all possible keys that could appear in a document in that collection; which may not be the case if the document type can contain an array or nested sub document. – Buttonhole

@Buttonhole you're right, forgot to mention this important fact. What I usually do, is run a map reduce script to collect all keys periodically and use it to pull all the keys – Myrtice

Answer 5

Easy export csv or json file With Mongo Compass tool

Mongo Compass As the GUI for MongoDB, MongoDB Compass allows you to make smarter decisions about document structure, querying, indexing, document validation, and more. Commercial subscriptions include technical support for MongoDB Compass. https://www.mongodb.com/try/download/compass

Clipboard

Answer 6

works for me remoting to a docker container with mongo:4.2.6

mongoexport -h mongodb:27017 --authenticationDatabase=admin -u username -p password -d database -c collection -q {"created_date": { "$gte": { "$date": "2020-08-03T00:00:00.000Z" }, "$lt": { "$date": "2020-08-09T23:59:59.999Z" } } } --fields=somefield1,somefield2 --type=csv --out=/archive.csv

Locket

Answer 7

I could not get mongoexport to do this for me. I found that,to get an exhaustive list of all the fields, you need to loop through the entire collection once. Use this to generate the headers. Then loop through the collection again to populate these headers for each document.

I've written a script to do just this. Converting MongoDB docs to csv irrespective of schema differences between individual documents.

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

Ramah

Answer 8

Also if you want to export inner json fields use dot (. operator).

JSON record:

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

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

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

Output csv:

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

Note: Make sure you do not export an array. It would corrupt the CSV format like field userIds shown above

Lie

Answer 9

Solution for MongoDB Atlas users!

Add the --fields parameter as comma separated field names enclosed in double inverted quotes:

--fields "<FIELD 1>,<FIELD 2>..."

This is complete example:

mongoexport --host Cluster0-shard-0/shard1URL.mongodb.net:27017,shard2URL.mongodb.net:27017,shard3URL.mongodb.net:27017 --ssl --username <USERNAME> --password <PASSWORD> --authenticationDatabase admin --db <DB NAME> --collection <COLLECTION NAME> --type <OUTPUT FILE TYPE> --out <OUTPUT FILE NAME> --fields "<FIELD 1>,<FIELD 2>..."

Bodycheck

Answer 10

This working for me Try it

mongoexport --host cluster0-shard-dummy-link.mongodb.net:27017 --db yourdbname --forceTableScan   --collection users --type json --out /var/www/html/user.json --authenticationDatabase admin --ssl --username Yourusername --password Yourpassword

Above cmd return whole data of the users collection if you want filter field then add --fields=email,name

Aslam

How is this different from the answers below? – Beer

mongodump --host cluster0-shard.mongodb.net:27017 --db dbname --forceTableScan --out /var/www/html/documents/db-12-2020 --authenticationDatabase admin --ssl --username youruname --password yourpassword you can try this also this gives you whole collections. – Aslam

Answer 11

For all those who are stuck with an error.

Let me give you guys a solution with a brief explanation of the same:-

command to connect:-

mongoexport --host your_host --port your_port -u your_username -p your_password --db your_db --collection your_collection --type=csv --out file_name.csv --fields all_the_fields --authenticationDatabase admin

--host --> host of Mongo server

--port --> port of Mongo server

-u --> username

-p --> password

--db --> db from which you want to export

--collection --> collection you want to export

--type --> type of export in my case CSV

--out --> file name where you want to export

--fields --> all the fields you want to export (don't give spaces in between two field name in between commas in case of CSV)

--authenticationDatabase --> database where all your user information is stored

Brummell

Answer 12

Below command used to export collection to CSV format.

Note: naag is database, employee1_json is a collection.

mongoexport --db naag--collection employee1_json --type csv --out /home/orienit/work/mongodb/employee1_csv_op1

Gaulish

this is what mongodb 4 return: Failed: CSV mode requires a field list – Carrier