mongodb 将 mongo 查询的输出重定向到 csv 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14478304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Redirect output of mongo query to a csv file
提问by Aafreen Sheikh
I am using MongoDB 2.2.2 for 32-bit Windows7 machine. I have a complex aggregation query in a .js file. I need to execute this file on the shell and direct the output to a CSV file. I ensure that the query returns a "flat" json (no nested keys), so it is inherently convertible to a neat csv.
I know about load()and eval(). eval()requires me to paste the whole query into the shell and allows only printjson()inside the script, while I need csv. And, the second way: load()..It prints the output on the screen, and again in json format.
Is there a way Mongo can do this conversion from json to csv? (I need csv file to prepare charts on the data). I am thinking:
1. Either mongo has a built-in command for this that I can't find right now.
2. Mongo can't do it for me; I can at most send the json output to a file which I then need to convert to csv myself.
3. Mongo can send the json output to a temporary collection, the contents of which can be easily mongoexportedto csv format. But I think only map-reduce queries support output collections. Is that right? I need it for an aggregation query.
我在 32 位 Windows7 机器上使用 MongoDB 2.2.2。我在 .js 文件中有一个复杂的聚合查询。我需要在 shell 上执行这个文件并将输出定向到一个 CSV 文件。我确保查询返回“平面”json（无嵌套键），因此它本质上可以转换为整洁的 csv。
我知道load()和eval()。eval()要求我将整个查询粘贴到 shell 中，并且只允许printjson()在脚本内部，而我需要 csv。而且，第二种方式：load()..它在屏幕上打印输出，并再次以 json 格式打印。
Mongo 有没有办法从 json 到 csv 进行这种转换？（我需要 csv 文件来准备数据图表）。我在想：
1. 要么 mongo 有一个我现在找不到的内置命令。
2. Mongo 不能为我做；我最多可以将 json 输出发送到一个文件，然后我需要自己将其转换为 csv。
3. Mongo 可以将json 输出发送到一个临时集合，其中的内容可以很容易地mongoexported转为csv 格式。但我认为只有 map-reduce 查询支持输出集合。那正确吗？我需要它来进行聚合查询。
Thanks for any help :)
谢谢你的帮助 ：）
回答by GEverding
I know this question is old but I spend an hour trying to export a complex query to csv and I wanted to share my thoughts. First I couldn't get any of the json to csv converters to work (although thisone looked promising). What I ended up doing was manually writing the csv file in my mongo script.
我知道这个问题很老，但我花了一个小时试图将复杂的查询导出到 csv，我想分享我的想法。首先，我无法让任何 json 到 csv 转换器工作（尽管这个看起来很有希望）。我最终做的是在我的 mongo 脚本中手动编写 csv 文件。
This is a simple version but essentially what I did:
这是一个简单的版本，但基本上是我所做的：
print("name,id,email");
db.User.find().forEach(function(user){
print(user.name+","+user._id.valueOf()+","+user.email);
});
This I just piped the query to stdout
这我只是将查询通过管道传输到标准输出
mongo test export.js > out.csv
where testis the name of the database I use.
test我使用的数据库的名称在哪里。
回答by thisarattr
Mongo's in-built export is working fine, unless you want to any data manipulation like format date, covert data types etc.
Mongo 的内置导出工作正常，除非您想要任何数据操作，如格式日期、隐蔽数据类型等。
Following command works as charm.
以下命令很有魅力。
mongoexport -h localhost -d databse -c collection --type=csv
--fields erpNum,orderId,time,status
-q '{"time":{"$gt":1438275600000}, "status":{"$ne" :"Cancelled"}}'
--out report.csv
回答by Lucky Soni
Extending other answers:
扩展其他答案：
I found @GEverding's answer most flexible. It also works with aggregation:
我发现@GEverding 的回答最灵活。它也适用于聚合：
test_db.js
test_db.js
print("name,email");
db.users.aggregate([
{ $match: {} }
]).forEach(function(user) {
print(user.name+","+user.email);
}
});
Execute the following command to export results:
执行以下命令导出结果：
mongo test_db < ./test_db.js >> ./test_db.csv
Unfortunately, it adds additional text to the CSV file which requires processing the file before we can use it:
不幸的是，它向 CSV 文件添加了额外的文本，这需要在我们使用它之前处理该文件：
MongoDB shell version: 3.2.10
connecting to: test_db
But we can make mongo shell stop spitting out those comments and only print what we have asked for by passing the --quietflag
但是我们可以让 mongo shell 停止吐出这些评论，只通过传递--quiet标志来打印我们要求的内容
mongo --quiet test_db < ./test_db.js >> ./test_db.csv
回答by Shirish Kumar
Here is what you can try:
您可以尝试以下方法：
print("id,name,startDate")
cursor = db.<collection_name>.find();
while (cursor.hasNext()) {
jsonObject = cursor.next();
print(jsonObject._id.valueOf() + "," + jsonObject.name + ",\"" + jsonObject.stateDate.toUTCString() +"\"")
}
Save that in a file, say "export.js". Run the following command:
将其保存在一个文件中，比如“export.js”。运行以下命令：
mongo <host>/<dbname> -u <username> -p <password> export.js > out.csv
回答by geakie
Have a look at this
看看 这个
for outputing from mongo shell to file. There is no support for outputing csv from mongos shell. You would have to write the javascript yourself or use one of the many converters available. Google "convert json to csv" for example.
用于从 mongo shell 输出到文件。不支持从 mongos shell 输出 csv。您必须自己编写 javascript 或使用许多可用的转换器之一。例如，谷歌“将 json 转换为 csv”。
