Node JS Import/Upload Excel to MySQL Database

Node JS Import/Upload Excel to MySQL Database

Import/upload excel file to MySQL database using node js + express; This tutorial will show you how to upload/Import Excel file data into MySQL database using Node js + express + multer+ read-excel-file.

Read, import and upload 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.

Node. js + Express + MySQL Upload/Import Excel file data into Database

  • 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 ‘readexcelfile‘.

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.

Recommended Node JS Tutorials

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *