javascript 如何使用nodejs将excel文件（XLSX）导入mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31651931/
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 mysql using nodejs
提问by Labeo
I am trying to import data in excel file to mysql just like row colon using nodejs are there any references i can learn or any module in nodejs that does my work or any sample code
我正在尝试将 excel 文件中的数据导入 mysql 就像使用 nodejs 的行冒号是否有任何我可以学习的参考或 nodejs 中的任何模块来完成我的工作或任何示例代码
I have searched in google but i have seen solutions for only mongodb and for python ,
我在谷歌搜索过，但我只看到了 mongodb 和 python 的解决方案，
回答by user3743222
A few methods which come to my mind:
我想到的几种方法：
Save the excel sheet(s) you want to import in csv format, and then import them into mysql : cf. How to import CSV file to MySQL tableBy far the simplest method, but you might run into trouble with quotes and commas and other idiosyncrasies.
use an javascript excel parser to read the excel file and perform directly with code the update in your mysql database : cf. https://github.com/SheetJS/js-xlsxThat option is more complex but it allows you to automate the task should you have many files to copy.
将要导入的excel表保存为csv格式，然后导入mysql：cf. 如何将 CSV 文件导入 MySQL 表到目前为止是最简单的方法，但您可能会遇到引号和逗号以及其他特性的麻烦。
使用 javascript excel 解析器读取 excel 文件并直接使用代码执行 mysql 数据库中的更新：cf. https://github.com/SheetJS/js-xlsx该选项更复杂，但如果您有许多文件要复制，它允许您自动执行任务。
回答by Megha T R
1.Upload the csv file from a form.
1.从表单上传csv文件。
2.Include your configuration file.
2.包括您的配置文件。
Take a look at the following code.
看看下面的代码。
var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var path = require('path');
var http = require('http');
var formidable = require('formidable');
var fs = require('fs');
var csvParser = require('csv-parse');
var cm = require('csv-mysql');
var connection=require('./config');
// Create application/x-www-form-urlencoded parser
var urlencodedParser = bodyParser.urlencoded({ extended: false })
app.use(express.static('public'));
app.get('/csvupload.html', function (req, res) {
res.sendFile(path.resolve("../view/csvupload.html"));
})
app.post('/csvfileupload', urlencodedParser, function (req, res) {
if (req.url == '/csvfileupload') {
var form = new formidable.IncomingForm();
form.parse(req, function (err, fields, files)
{
var oldpath = files.filetoupload.path;
var newpath = 'C:/xampp/htdocs/nodejs/mvc/csvupload/uploads/' + files.filetoupload.name;
fs.rename(oldpath, newpath, function (err) {
if (err) throw err;
res.write('File uploaded and moved!');
res.end();
});
fs.readFile(oldpath, { encoding: 'utf-8' }, function(err, csvData) {
if (err)
{
console.log(err);
}
csvParser(csvData, { delimiter: ',' }, function(err, data) {
if (err)
{
console.log(err);
}
else
{
console.log(data);
var sql = "INSERT INTO customer (id,name,address,designation) VALUES ?";
connection.query(sql, [data], function(err) {
if (err) throw err;
conn.end();
});
}
});
});
});
}
}).listen(8080);
回答by Riz-waan
I have developed a plugin that does this. Here is the link to the npm module: https://www.npmjs.com/package/xlsx-mysql
我开发了一个插件来做到这一点。这是 npm 模块的链接：https://www.npmjs.com/package/xlsx-mysql
And the github link: https://github.com/Rizwaan-Company/xlsx-mysql
和 github 链接：https: //github.com/Rizwaan-Company/xlsx-mysql
Sample Code:
示例代码：
const XLSXtoMYSQL = require('xlsx-mysql');
var optionsZ = {
mysql: {
host: '127.0.0.1',
user: 'root',
database: 'test',
password: 'password',
port: '3306'
},
csv: {
delimiter: '+'
}
}
var locationZ = __dirname + '/file.xlsx';
var waitT = 1000;
XLSXtoMYSQL(locationZ,optionsZ,waitT);
