In this tutorial, you will learn how to upload/Import/ Excel file data to MySQL database in Node js express using multer and read-excel-file.
How to Import/Upload Excel File Data into MySQL Database in Node Js express using Multer
Steps to import/upload/insert excel file data in MySQL database using node js express using multer:
Step 1 – Create Node Express js App
Run the following command on terminal to create node js app:
mkdir my-app cd my-app npm init -y
Step 2 – Create Table in MySQL Database
Run the following sql query to create a table into your database:
CREATE TABLE `customer` ( `id` bigint(20) NOT NULL, `address` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
Step 3 – Install Required Packages
Run the following command on the terminal to express multer, ejs, body-parser, read-execel-file, MySQL dependencies :
npm install express multer body-parser mysql read-excel-file
Step 4 – Create Excel File Upload Form
Create a index.html file and in this file implement form with a `file input` element that allows users to choose the Excel file for upload:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Node js upload/Import excel file to MySQL database - Tutsmake.com</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<h1>Node js upload Excel file to MySQL database - Tutsmake.com</h1>
<form action="/uploadfile" enctype="multipart/form-data" method="post">
<input type="file" name="uploadfile" accept='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel' >
<input type="submit" value="Upload Excel">
</form>
</body>
</html>
Make sure your form must have enctype="multipart/form-data"attribute and form method should be post.
Step 5 – Create Server.js File
Create server.js file and import express multer body-parser mysql dependencies in server.js; as shown below:
const express = require('express')
const app = express()
const bodyparser = require('body-parser')
const fs = require('fs');
const readXlsxFile = require('read-excel-file/node');
const mysql = require('mysql')
const multer = require('multer')
const path = require('path')
//use express static folder
app.use(express.static("./public"))
// body-parser middleware use
app.use(bodyparser.json())
app.use(bodyparser.urlencoded({
extended: true
}))
// Database connection
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "test"
})
db.connect(function (err) {
if (err) {
return console.error('error: ' + err.message);
}
console.log('Connected to the MySQL server.');
})
// Multer Upload Storage
const storage = multer.diskStorage({
destination: (req, file, cb) => {
cb(null, __basedir + '/uploads/')
},
filename: (req, file, cb) => {
cb(null, file.fieldname + "-" + Date.now() + "-" + file.originalname)
}
});
const upload = multer({storage: storage});
//! Routes start
//route for Home page
app.get('/', (req, res) => {
res.sendFile(__dirname + '/index.html');
});
// -> Express Upload RestAPIs
app.post('/uploadfile', upload.single("uploadfile"), (req, res) =>{
importExcelData2MySQL(__basedir + '/uploads/' + req.file.filename);
console.log(res);
});
// -> Import Excel Data to MySQL database
function importExcelData2MySQL(filePath){
// File path.
readXlsxFile(filePath).then((rows) => {
// `rows` is an array of rows
// each row being an array of cells.
console.log(rows);
/**
[ [ 'Id', 'Name', 'Address', 'Age' ],
[ 1, 'john Smith', 'London', 25 ],
[ 2, 'Ahman Johnson', 'New York', 26 ]
*/
// Remove Header ROW
rows.shift();
// Open the MySQL connection
db.connect((error) => {
if (error) {
console.error(error);
} else {
let query = 'INSERT INTO customer (id, address, name, age) VALUES ?';
connection.query(query, [rows], (error, response) => {
console.log(error || response);
/**
OkPacket {
fieldCount: 0,
affectedRows: 5,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '&Records: 5 Duplicates: 0 Warnings: 0',
protocol41: true,
changedRows: 0 }
*/
});
}
});
})
}
// Create a Server
let server = app.listen(8080, function () {
let host = server.address().address
let port = server.address().port
console.log("App listening at http://%s:%s", host, port)
})
Step 6 – Start App Server
You can use the following command to start node js upload/import Excel file to MySQL database app server:
//run the below command npm start after run this command open your browser and hit http://127.0.0.1:3000/
Conclusion
Upload/Import Excel file in MySQL database using node js; In this tutorial, you have learned how to upload/import Excel file data into MySQL database using Node js + express + multer + read-excel-file.
This was a great help.
thanks for the articale.