15-Dec-2022
.
Admin
Hello Friends,
The Excel file is a spreadsheet file format created by Microsoft for use with Microsoft Excel. The Excel file is a spreadsheet file format created by Microsoft for use with Microsoft Excel. We will teach how to upload Excel file data to the MySQL database using the Node js
Read, 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 an excel file in the node js app and how to import excel file data into the MySQL database.
Step 1: Create Node Express js App
Execute the following command on the terminal to create the 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 in 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-excel-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 makes it available under req.body property. In other words, it simplifies incoming requests.
Read-Excel-File – Read small to medium `*.xlsx` files in a browser or Node.js. Parse to JSON with 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 the 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 an index.html file and add the following code into it:
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to Import Excel File Records in MySQL Database using Node Js?</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<h1>How to Import Excel File Records in MySQL Database using Node Js?</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 the form method should be posted.
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 running this command open your browser and hit
http://127.0.0.1:3000/
I hope it can help you...
#Node JS
