mongodb 如何将excel文件（XLSX）导入mongoDB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25241500/
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
how to import excel file (XLSX) to mongoDB
提问by SUNDARRAJAN K
I have a set of data as input to be given to MongoDB in XLSX format. How am I supposed to import the Excel file as input to MongoDB?
我有一组数据作为要以 XLSX 格式提供给 MongoDB 的输入。我应该如何将 Excel 文件作为输入导入到 MongoDB？
Is there any plugin available to import xlsx files as input to MongoDB?
是否有任何插件可用于将 xlsx 文件作为输入导入到 MongoDB？
回答by Juan Carlos Farah
You cannot import an XLSX file into MongoDB directly. However, what you can do with an Excel spreadsheet is save it as a CSV file, then use mongoimportto import it into MongoDB. You can find the documentation for mongoimporthere, but in any case, the command you need to run should look something like the following:
您不能直接将 XLSX 文件导入 MongoDB。但是，您可以使用 Excel 电子表格将其保存为 CSV 文件，然后用于mongoimport将其导入 MongoDB。您可以在mongoimport此处找到文档，但无论如何，您需要运行的命令应如下所示：
mongoimport --db myDb --collection myCollection --type csv --headerline --file /path/to/myfile.csv
In the command above, the --headerlineflag indicates that the first line in your file contains the name of the fields. There are many other options you can use depending on your needs. These are highlighted in the documentation.
在上面的命令中，该--headerline标志表示文件中的第一行包含字段的名称。您可以根据需要使用许多其他选项。这些在文档中突出显示。
回答by SUNDARRAJAN K
I used "fast-csv"to upload csv onto mongoDB database.
我使用“fast-csv”将 csv 上传到 mongoDB 数据库。
Sample Code:
示例代码：
var csv = require("fast-csv");
csv.fromPath('./test.csv',{headers: true})
.on("data", function(data){
var details = new Details;
details=data;
details.save(function (saveErr, savedetail) {
if (saveErr) {
console.log(saveErr)
}
});
})
.on("end", function(){
console.log("done");
})
回答by Sumeet Gawas
You can upload data of multiple sheetsfrom one excel into mongodb at once using this code.
Your first row i.e "0" row will be consider as column title and rest data of that column.
您可以使用此代码一次将多个工作表的数据从一个 excel上传到 mongodb。
您的第一行即“0”行将被视为该列的列标题和其余数据。
MongoClient mongoClient = new MongoClient( "localhost" , 27017 );
DB db = mongoClient.getDB("yourdb_name");
System.out.println("Connected to Database successfully");
DBCollection coll = db.getCollection("your_collection name");
System.out.println("Collection your_collection name selected successfully");
DBCollection OR = db.getCollection("Input_Container");
System.out.println("Collection Device_Details selected successfully");
OR.drop();
DBObject arg1 = null;
//coll.update(query, update);
DBCollection OR_UPLOAD =
db.createCollection("Input_Container", arg1);
String path =" your file path";
File myFile = new File(path);
FileInputStream inputStream = new FileInputStream(myFile);
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
int number=workbook.getNumberOfSheets();
System.out.println("NumberOfSheets "+number);
for(int i=0;i<number;i++)
{
XSSFSheet sheet = workbook.getSheetAt(i);
int col_value=sheet.getRow(0).getLastCellNum();
int row_num= sheet.getLastRowNum();
System.out.println("row_num "+row_num);
List<String> DBheader = new ArrayList<String>();
List<String> Data = new ArrayList<String>();
for(int z=1;z<=row_num;z++){
DBheader.clear();
Data.clear();
for(int j=0;j<col_value;j++)
{
if(sheet.getRow(0).getCell(j).toString()!=null || sheet.getRow(0)!=null)
{
String cel_value = sheet.getRow(0).getCell(j).toString();
DBheader.add(cel_value.trim());
}
else{
break;
}
}
for(int k=0;k<col_value;k++){
String data =" ";
if(sheet.getRow(z).getCell(k)!=null)
{
data = sheet.getRow(z).getCell(k).toString();
}
Data.add(data.trim());
}
BasicDBObject doc = new BasicDBObject();
System.out.println("Data.size() "+Data.size());
int l=0;
for(String headers:DBheader)
{
if(l>Data.size()){break;}
doc.append(headers, Data.get(l));
l++;
}
OR_UPLOAD.insert(doc);
}
}System.out.println("File Upload Done");
mongoClient.close();
回答by nickmilon
There is a pymongo extention utilities package, one of the modules there does exactly this, imports an excel file to a mongo collection or a complete excel workbook to a mongo database.
You can find documentation and examples here:
and you can install the library with `pip install mongoUtils
有一个 pymongo 扩展实用程序包，其中一个模块就是这样做的，将 excel 文件导入 mongo 集合或将完整的 excel 工作簿导入 mongo 数据库。您可以在此处找到文档和示例：
您可以使用`pip install mongoUtils 安装该库
- you will need to also install xldr library
- disclaimer: I am the author of this library
- 您还需要安装 xldr 库
- 免责声明：我是这个库的作者
回答by Gokul
In case the DB server is not local, You will have to specify the host, port, username, password along with the DB name, collection name.
如果数据库服务器不是本地的，您必须指定主机、端口、用户名、密码以及数据库名称、集合名称。
mongoimport --host <hostname>:<port> --username <username> --password <password> --db <db name> --collection <collection name> --type csv --headerline --file /path/to/myfile.csv
回答by arnav
mongoimport -d admin -c Mongocsv --type csv --file Mongocsv.csv --headerline
mongoimport -d admin -c Mongocsv --type csv --file Mongocsv.csv --headerline
connected to: 127.0.0.1 imported 5 objects
连接到：127.0.0.1 导入了 5 个对象
mongo
蒙戈
use admin switched to db admin
db.Mongocsv.find()
使用 admin 切换到 db admin
db.Mongocsv.find()
回答by Andrey
You can handle loading the Excel file content by writing Java code using Apache POI library (https://poi.apache.org/). The library is developed for working with MS office application data including Excel.
您可以通过使用 Apache POI 库 ( https://poi.apache.org/)编写 Java 代码来处理加载 Excel 文件内容。该库是为处理包括 Excel 在内的 MS Office 应用程序数据而开发的。
I have recently created the application based on the technology that will help you to load Excel files to the MongoDB database.
我最近基于可帮助您将 Excel 文件加载到 MongoDB 数据库的技术创建了该应用程序。
The application is available under http://www.abespalov.com/and tested only for Windows, but should work for Linux as well. The application will create necessary collection automatically and populate the collection with the Excel file content. You can export several files in parallel. You can skip the step to convert the files into the CSV format. The application handles the xls and xlsx formats.
该应用程序可在http://www.abespalov.com/下获得， 并且仅针对 Windows 进行了测试，但也应该适用于 Linux。该应用程序将自动创建必要的集合并使用 Excel 文件内容填充该集合。您可以并行导出多个文件。您可以跳过将文件转换为 CSV 格式的步骤。该应用程序处理 xls 和 xlsx 格式。
Overall application stages are :
整体应用阶段是：
1)Load the excel file content. Here is the code depending on file extension:
1)加载excel文件内容。这是取决于文件扩展名的代码：
fileExtension = FilenameUtils.getExtension(inputSheetFile.getName());
if (fileExtension.equalsIgnoreCase("xlsx")) {
workbook = createWorkbook(openOPCPackage(inputSheetFile));
} else {
workbook = createWorkbook(openNPOIFSFileSystemPackage(inputSheetFile));
}
sheet = workbook.getSheetAt(0);
2)Establish the MongoDB connection. I use the MongoClientURI library;
2）建立MongoDB连接。我使用 MongoClientURI 库；
MongoClientURI mongoClientURI = new MongoClientURI(
"mongodb://" + dbUser + ":" + dbPassword + "@" + dbServer
+ ":" + dbPort + "/" + dbDatabase);
excel2db.mongoClient = new MongoClient(mongoClientURI);
3)Iterate over the sheet and inset rows into the collection. Here is a piece of Java code :
3)遍历工作表并将行插入到集合中。这是一段Java代码：
Row row = (Row) rowIterator.next();
//get column names from a header
short minColIdx = row.getFirstCellNum();
short maxColIdx = row.getLastCellNum();
ArrayList<String> columnNameList = new ArrayList();
String columnName;
logger.info("The table {} is being populated", tableName);
//populate a list of column names
for (short colIdx = minColIdx; colIdx < maxColIdx; colIdx = (short) (colIdx + 1)) {
columnNameList.add(row.getCell(colIdx) == null? "": row.getCell(colIdx).toString());
}
while (rowIterator.hasNext()) {
Document document = new Document();
Row rowData = (Row) rowIterator.next();
numOfProcessedRows++;
for (short colIdx = minColIdx; colIdx < maxColIdx; colIdx = (short) (colIdx + 1)) {
document.put(columnNameList.get(colIdx), rowData.getCell(colIdx).toString());
}
//save the document into a collection, point to the database
MongoCollection mongoCollection = mongoDB.getCollection(tableName);
mongoCollection.insertOne(document);
}
}
Here you can find all Java code for the application created for exporting excel to Postgres (https://github.com/palych-piter/Excel2DB).
在这里，您可以找到为将 excel 导出到 Postgres ( https://github.com/palych-piter/Excel2DB) 而创建的应用程序的所有 Java 代码。
