Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Export all of Mongodb collections as csv without the need to specify fields
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;
@FJLopezGarcia

This comment has been minimized.

Copy link

commented Jul 2, 2014

can we do that in a .bat file to be executed from cmd ??

@sterichards

This comment has been minimized.

Copy link

commented Mar 12, 2015

It wants to connect to 127.0.0.1 even after I set the variables:

Stephens-MacBook-Pro:mongo stephenrichards$ ./export.sh
exception: connect failed
exporting collection MongoDB shell version: 2.6.5
connecting to: ibmavnet
2015-03-12T11:34:06.875+0000 warning: Failed to connect to 127.0.0.1:27017
connected to: ds035240.mongolab.com:35240
exported 1 records
exporting collection reason: errno:61 Connection refused
2015-03-12T11:34:06.876+0000 Error: couldn't connect to server 127.0.0.1:27017 (127.0.0.1)

@vaishaleee

This comment has been minimized.

Copy link

commented Mar 28, 2016

Try with commenting out line no. 12

@prem-narain

This comment has been minimized.

Copy link

commented Mar 14, 2017

Just Comment "user=USERNAME", "pass=PASSWORD" and remove "-u $user" "-p $pass" will work fine.

@ChrisKader

This comment has been minimized.

Copy link

commented Mar 6, 2018

#!/bin/bash
OIFS=$IFS;
IFS=",";
# fill in your details here
dbname=DATABASE
user=USER
pass=PASSOWRD
host="localhost"
port="27017"
#Change this to the DB you are dumping if you have the user created at the DB level.
authdb="root"

# first get all collections in the database
collectionArray=$(mongo $dbname --host $host --port $port -u $user -p $pass --authenticationDatabase $authdb --eval 'db.getCollectionNames().join('');' --quiet);

# for each collection
for col in $collectionArray;
do
    echo 'exporting collection' $col
    # 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 $dbname --host $host --port $port -u $user -p $pass --authenticationDatabase $authdb --eval "function z(c,e){var a=[];var d=Object.keys(c);for(var f in d){var b=d[f];if(typeof c[b]==='object'){var g=[],h=z(c[b],e+'.'+b);a=g.concat(a,h);}else a.push(e+'.'+b);}return a;}var a=[],b=db.$col.findOne({}),c=Object.keys(b);for(var i in c){var j=c[i];if(typeof b[j]==='object'&&j!='_id'){var t1=[],t2=z(b[j],j);a=t1.concat(a,t2);}else a.push(j);}a.join(',');" --quiet`
    # now use mongoexport with the set of keys to export the collection to csv
    mongoexport --host $host --port $port -u $user -p $pass --authenticationDatabase $authdb -d $dbname -c $col --fields "$keys" --type=csv --out $dbname.$col.csv --authenticationDatabase wowtokenio;
done

IFS=$OIFS;

I modified the code a bit:

  1. Corrected the connection string format to account for mongodb's changes.
  2. Modified the javascript to include keys of sub-objects within the document.
{
    "_id" : "5a893606c66f97364f7564bb",
    "regionName" : "KR",
    "regionNameBlizzard" : "kr",
    "subRegionCount" : 1,
    "regionCollectionName" : "krPrices",
    "regionNameFriendly" : "Korea",
    "subRegions" : {
            "subRegion_KRW" : {
                    "subRegionISO" : "KR",
                    "subRegionName" : "Korea",
                    "subRegionCurrencyCode" : "KRW",
                    "subRegionCurrencyName" : "South Korean won",
                    "subRegionCurrencySymbol" : "₩",
                    "subRegionBNetPurchasePrice" : 22000,
                    "subRegionBNetBalanceValue" : 0,
                    "subRegionBNetExchangeSupported" : false
            }
    }
}
The above document yields the below fields:
[ '_id',
  'regionName',
  'regionNameBlizzard',
  'subRegionCount',
  'regionCollectionName',
  'regionNameFriendly',
  'subRegions.subRegion_KRW.subRegionISO',
  'subRegions.subRegion_KRW.subRegionName',
  'subRegions.subRegion_KRW.subRegionCurrencyCode',
  'subRegions.subRegion_KRW.subRegionCurrencyName',
  'subRegions.subRegion_KRW.subRegionCurrencySymbol',
  'subRegions.subRegion_KRW.subRegionBNetPurchasePrice',
  'subRegions.subRegion_KRW.subRegionBNetBalanceValue',
  'subRegions.subRegion_KRW.subRegionBNetExchangeSupported' ]
@Hrishi0929

This comment has been minimized.

Copy link

commented Jun 28, 2018

how can we do the same thing using javascript..??

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.