mderazon/mongo-dump-csv.sh

vaishaleee · 2016-03-28T12:58:55Z

Try with commenting out line no. 12

prem-narain · 2017-03-14T10:27:12Z

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

ChrisKader · 2018-03-06T22:03:42Z

#!/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:

Corrected the connection string format to account for mongodb's changes.
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 · 2018-06-28T11:30:16Z

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

alexivkin · 2020-05-06T05:09:45Z

Simplified further to avoid messing with IFS, fixed small bug on "null" in z() and modified to work on a mongo docker container

collections=$(docker exec mongo mongo $DB --quiet --eval 'rs.slaveOk();db.getCollectionNames().join(" ");')
for col in $collections; 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=$(docker exec mongo mongo $DB --quiet --eval "function z(c,e){if(c===null){return 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(',');")
    # now use mongoexport with the set of keys to export the collection to csv
    docker exec mongo mongoexport --quiet -d $DB -c $col --fields "$keys" --type=csv --out "/dump/$col.csv"
done

smsajjadzaidi · 2020-06-08T11:42:06Z

2020-06-08T03:35:32.886-0800 E QUERY [js] SyntaxError: missing } after function body @(shell eval):1:388

It returned the above error in output file, do you have any idea about what is the problem here?

muhlucas · 2020-11-11T22:49:54Z

If the name of your collection had a hyphen the export will return undefined.

The code that fixed it.

#!/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. e.g AWS cluster is admin
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 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){if(c===null || c === undefined){return e};var a=[];var d=Object.keys(c);for(var f in d){var b=d[f];if(b != undefined && c != undefined && 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 -d $dbname --authenticationDatabase $authdb -c "$col" --fields "$keys" --type csv --out $dbname."$col".csv;
done

IFS=$OIFS;

rmcpp · 2020-12-18T18:08:15Z

when "eval" the code without "--quiet" symbol, some unexpected words will occours in your result. So I fixed this problem and my solution is below:


OIFS=$IFS;
IFS=",";

# fill in your details here
dbname=yourdbname
host=127.0.0.1:27017

# first get all collections in the database
collections=`mongo "$host/$dbname" --quiet  --eval "rs.slaveOk();db.getCollectionNames();"`;
collections=`mongo --quiet $dbname --eval "rs.slaveOk();var names=db.getCollectionNames().join(','); names"` ;
echo $collections;

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" --eval "rs.slaveOk();var keys = []; for(var key in db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]) { keys.push(key); }; keys.join(',');" --quiet`;
    echo $keys;
    # now use mongoexport with the set of keys to export the collection to csv
    mongoexport --host $host -d $dbname -c ${collectionArray[$i]} --fields "$keys" --type=csv --out ${collectionArray[$i]}.csv;
done

IFS=$OIFS;

mail2shobz · 2021-04-22T12:24:55Z

#!/bin/bash
OIFS=$IFS;
IFS=",";
#Accepting Entry form the user
printf 'Enter the DB name you want convert to CSV:'
read -r dbname
dbname=$dbname
host=127.0.0.1:27017

# first get all collections in the database
collections=mongo "$host/$dbname" --quiet --eval "rs.secondaryOk();db.getCollectionNames();";
collections=mongo --quiet $dbname --eval "rs.secondaryOk();var names=db.getCollectionNames().join(','); names" ;
echo $collections;

collectionArray=($collections);

# for each collection
for ((i=0; i<${#collectionArray[@]}; ++i));
do
echo 'exporting collection' ${collectionArray[$i]}
keys=mongo "$host/$dbname" --eval "rs.secondaryOk();var keys = []; for(var key in db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]) { keys.push(key); }; keys.join(',');" --quiet;
echo $keys;
# now use mongoexport with the set of keys to export the collection to csv
mongoexport --host $host -d $dbname -c ${collectionArray[$i]} --fields "$keys" --type=csv --out ${collectionArray[$i]}.csv --forceTableScan;
done

IFS=$OIFS;
#Tested in latest version of Mongo DB it will successfully generate csv of all colections

sandeep2244 · 2021-04-29T16:21:56Z

Hey @muhlucas , what if i want get keys of last inserted docs ? , where i can add sort ?

muhlucas · 2021-05-19T15:23:41Z

Hey @muhlucas , what if i want get keys of last inserted docs ? , where i can add sort ?

@sandeep2244 You could try to edit the filter to get keys.

The below code is part of the current script:

keys=mongo "$host/$dbname" --eval "rs.slaveOk();var keys = []; for(var key in db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]) { keys.push(key); }; keys.join(',');" --quiet; echo $keys

Change the following part to add your filter and get the last keys of a collection.
db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]

liufuzhu · 2021-07-27T13:05:47Z

this shit does not work

adamtaiti · 2021-10-29T17:17:12Z

I tried it and it is working fine, except for date fields. They are not being exported! Any idea why is that?
Appreciate any help!

maksimsaroka · 2022-01-19T09:21:55Z

@adamtaiti here is code with fix for exporting dates:

keys=mongo $dbname --host "$host" --port "$port" -u "$user" -p "$pass" --authenticationDatabase "$authdb" --ssl --eval "function z(c,e){if(c===null || c === undefined || c instanceof Date){return e};var a=[];var d=Object.keys(c);for(var f in d){var b=d[f];if(b != undefined && c != undefined && 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;

PratikNalage · 2022-02-08T18:34:31Z

I'm unable to retrieve the Id, because of the following error
"2022-02-08T13:30:02.767-0500 I CONTROL [thread1] machdep.cpu.extfeatures unavailable _id"

mderazon/mongo-dump-csv.sh

vaishaleee commented Mar 28, 2016

prem-narain commented Mar 14, 2017

ChrisKader commented Mar 6, 2018 •

edited

Loading

Hrishi0929 commented Jun 28, 2018

alexivkin commented May 6, 2020 •

edited

Loading

smsajjadzaidi commented Jun 8, 2020 •

edited

Loading

muhlucas commented Nov 11, 2020

rmcpp commented Dec 18, 2020 •

edited

Loading

mail2shobz commented Apr 22, 2021 •

edited

Loading

sandeep2244 commented Apr 29, 2021 •

edited

Loading

muhlucas commented May 19, 2021 •

edited

Loading

liufuzhu commented Jul 27, 2021

adamtaiti commented Oct 29, 2021

maksimsaroka commented Jan 19, 2022

PratikNalage commented Feb 8, 2022

	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;

mderazon/mongo-dump-csv.sh

vaishaleee commented Mar 28, 2016

prem-narain commented Mar 14, 2017

ChrisKader commented Mar 6, 2018 • edited Loading

Hrishi0929 commented Jun 28, 2018

alexivkin commented May 6, 2020 • edited Loading

smsajjadzaidi commented Jun 8, 2020 • edited Loading

2020-06-08T03:35:32.886-0800 E QUERY [js] SyntaxError: missing } after function body @(shell eval):1:388

muhlucas commented Nov 11, 2020

rmcpp commented Dec 18, 2020 • edited Loading

mail2shobz commented Apr 22, 2021 • edited Loading

sandeep2244 commented Apr 29, 2021 • edited Loading

muhlucas commented May 19, 2021 • edited Loading

liufuzhu commented Jul 27, 2021

adamtaiti commented Oct 29, 2021

maksimsaroka commented Jan 19, 2022

PratikNalage commented Feb 8, 2022

ChrisKader commented Mar 6, 2018 •

edited

Loading

alexivkin commented May 6, 2020 •

edited

Loading

smsajjadzaidi commented Jun 8, 2020 •

edited

Loading

rmcpp commented Dec 18, 2020 •

edited

Loading

mail2shobz commented Apr 22, 2021 •

edited

Loading

sandeep2244 commented Apr 29, 2021 •

edited

Loading

muhlucas commented May 19, 2021 •

edited

Loading