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  gt  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

User · Answer

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

User · Answer

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 lt    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

User · Answer

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

User · Answer

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 -- gt  host of Mongo server --port -- gt  port of Mongo server -u -- gt  username -p -- gt  password --db -- gt  db from which you want to export --collection -- gt  collection you want to export --type -- gt  type of export in my case CSV --out -- gt  file name where you want to export --fields -- gt  all the fields you want to export  don t give spaces in between two field name in between commas in case of CSV  --authenticationDatabase -- gt  database where all your user information is stored

User · Answer

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

User · Answer

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   quot created date quot      quot  gte quot      quot  date quot    quot 2020-08-03T00 00 00 000Z quot      quot  lt quot      quot  date quot    quot 2020-08-09T23 59 59 999Z quot        --fields somefield1 somefield2 --type csv --out  archive csv

User · Answer

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

User · Answer

Solution for MongoDB Atlas users   Add the --fields parameter as comma separated field names enclosed in double inverted quotes   --fields   lt FIELD 1 gt   lt FIELD 2 gt        This is complete example   mongoexport --host Cluster0-shard-0 shard1URL mongodb net 27017 shard2URL mongodb net 27017 shard3URL mongodb net 27017 --ssl --username  lt USERNAME gt  --password  lt PASSWORD gt  --authenticationDatabase admin --db  lt DB NAME gt  --collection  lt COLLECTION NAME gt  --type  lt OUTPUT FILE TYPE gt  --out  lt OUTPUT FILE NAME gt  --fields   lt FIELD 1 gt   lt FIELD 2 gt

User · Answer

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

User · Answer

Also  you are not allowed spaces between comma separated field names   BAD  -f firstname  lastname  GOOD  -f firstname lastname

User · Answer

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

[mongodb] How to export collection to CSV in MongoDB?

Examples related to mongodb

Examples related to csv

Examples related to schemaless

Examples related to database