Node JS Express Upload Excel Data to MySQL Database Tutorial

Node JS Express Upload Excel Data to MySQL Database Tutorial

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.

Recommended Node JS Tutorials

AuthorAdmin

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including 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. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

One reply to Node JS Express Upload Excel Data to MySQL Database Tutorial

  1. This was a great help.
    thanks for the articale.

Leave a Reply

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