MongoDB中的資料匯出為excel CSV 檔案

標籤：nta   apple   cto   bubuko   ESS   one   app   field   oda   

1、開啟命令列，進入我們所安裝的mongodb路徑下的bin檔案夾

2、我們採用bin檔案夾下的mongoexport方法進行匯出， 

1	mongoexport -d myDB -c user -f _id,name,password,adress --csv -o ./user.csv

1. -d  標示 資料庫  
2.  -c   標示  資料表  
3.  -f   需要提取的field用逗號分隔  
4.  -o  輸出路徑  

 

 

ongoexport query with using date30 Mar 2014

Sometimes we might want to export only a specific part of our collection with query support of mongoexport.

Suppose this is our notebook collection, and each document refers to a notebook with their production date.

{        "_id" : ObjectId("531ce460000000019b9643bc"),        "company" : "Samsung",        "date" : ISODate("2014-03-09T22:00:00Z"),        "price" : 2000,        "brand" : "Ultrabook",}{        "_id" : ObjectId("531ce460000000019b9643ba"),        "company" : "Sony",        "date" : ISODate("2014-03-08T22:00:00Z"),        "price" : 1500,        "brand" : "Vaio",}{        "_id" : ObjectId("531ce460000000019b9643bd"),        "company" : "Apple",        "date" : ISODate("2014-03-07T22:00:00Z"),        "price" : 2250,        "brand" : "MacbookPro",}{        "_id" : ObjectId("531ce460000000019b9643be"),        "company" : "Apple",        "date" : ISODate("2014-03-06T22:00:00Z"),        "price" : 1200,        "brand" : "MacbookAir",}{        "_id" : ObjectId("531ce460000000019b9643bf"),        "company" : "Samsung",        "date" : ISODate("2014-03-05T22:00:00Z"),        "price" : 1000,        "brand" : "Ultrabook",}

The original way of mongoexport is defined by

mongoexport --db <database> --collection <collection> --query <JSON query> --out <file>

The major problem is we can not use ISODate(“”) objects to represent dates within a query, so that we have to convert each of them object into a Date object.

For instance; if we try to find the notebooks produced between 2014-03-09T22:00:00Z and 2014-03-07T22:00:00Z by Apple with the given query; 

mongoexport --db test --collection notebooks --query  ‘{ company:"Apple", date: { $lt: ISODate("2014-03-09T22:00:00Z") , $gte: ISODate("2014-03-07T22:00:00Z")} }‘ --out example.json

we will probably have an error like;

ERROR: too many positional options

There are two common ways to convert ISODates into Date objects which are;

• we can use a simple javascript in mongo shell like;

var a = ISODate(‘2014-03-10T22:00:00Z‘);a.getTime()

• we can convert the given date into milliseconds from the link ISODate to milliseconds

Now we have correct Date times to use them in our query.

mongoexport --db test --collection notebooks --query  "{ company:"Apple", date: { $lt: new Date(1394402400000) , $gte: new Date(1394229600000)} }" --out example.json

Finally, we will have a json file (example.json) in our current directory which includes only one document which is;

{        "_id" : ObjectId("531ce460000000019b9643bd"),        "company" : "Apple",        "date" : ISODate("2014-03-07T22:00:00Z"),        "price" : 2250,        "brand" : "MacbookPro",}

生產環境匯出命令：   

MongoDB中的資料匯出為excel CSV 檔案