Import/upload/insert excel file data to MySQL database using node js + express; This tutorial will show you how to upload/Import/insert Excel file data to MySQL database in Node js express using multer and read-excel-file.
Reading, importing, and uploading an excel file is a common task in the node js app. So in this tutorial, you will learn how to upload or insert Excel file data into the MySQL database using node js express and multer.
How to Import/Upload Excel File Data into MySQL Database in Node Js express using Multer
Let’s follow the following steps to import/upload/insert excel file data in MySQL database using node js express using multer:
- Step 1 – Create Node Express js App
- Step 2 – Create Table in MySQL Database
- Step 3 – Install Required Packages
- Step 4 – Create Excel File Upload Form
- Step 5 – Create Server.js File
- Step 6 – Start App Server
Step 1 – Create Node Express js App
Execute 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
Execute 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
Execute 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
body-parser – Node.js request body parsing middleware which parses the incoming request body before your handlers, and make it available under req.body property. In other words, it simplifies the incoming request.
Read-Excel-File – Read small to medium `*.xlsx` files in a browser or Node.js. Parse to JSON with a strict. import readXlsxFile from ‘read–excel–file‘.
Multer – Multer is a node.js middleware for handling multipart/form-data , which is primarily used for uploading files. It is written on top of busboy for maximum efficiency.
MySQL – A node.js driver for MySQL. It is written in JavaScript, does not require compiling, and is 100% MIT licensed.
Step 4 – Create Excel File Upload Form
Create a form with a `file input` element that allows us to choose the Excel file and a button to submit the form; So create index.html file and add the following code into it:
<!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.