How to Upload Excel to MySQL Database in Node JS

Node excel file upload in MySQL database tutorial; Excel offers a great way to manage a large set of data; it is used for maintaining various types of data.

In this tutorial, you will learn how to import Excel file records in the MySQL database through the Node js application.

Microsoft Excel is a popular software developed by Microsoft. It lets users manage, format and calculate data with formulas using a spreadsheet system. We will need an excel file with some records to upload to the MySQL database.

MySQL is a relational database management system (RDBMS). It is based on SQL (structured query language). It allows us to add, access, and process data for web app development.

To upload excel file data in MySQL database in Node, we will install and use express js, multer, read-excel-file, mysql and body-parser modules.

read-excel-file: Read small to medium *.xlsx files in a browser or Node.js. Parse to JSON with a strict schema.

express: It is used for creating server-side logic for web and mobile applications, we will use this for building routes.

multer: Multer is a node.js middleware for handling multipart/form-data, primarily used for uploading files.

body-parser: It is used for parsing the incoming request bodies in a middleware.

Node JS Import Excel Data to MySQL Database with Multer Example

  • Step 1: Set Up Node App
  • Step 2: Add Required Packages
  • Step 3: Create Table in MySQL
  • Step 4: Build File Upload Form
  • Step 5: Set Up Node Server
  • Step 6: Run Node Server

Set Up Node App

By running the given command create a new empty project directory.

mkdir node-excel

Move inside the project folder:

cd node-excel

Type the given command, then execute the provided command and let the package.json file generate.

npm init

Add Required Packages

Next, we require to invoke the suggested command to install the express read-excel-file multer mysql body-parser libraries.

npm install express read-excel-file multer mysql body-parser nodemon

Next, you require to create the app.js file and register this file name into scripts property in package.json file.

  "scripts": {
    "start": "node app.js"
  },

To save the file uploaded by multer client, hence create the uploads folder in your node application.

Create Table in MySQL

Creating a new table in the MySQL database is easy; all it requires a simple SQL query.

Hence, you have to copy the given query and execute it from the phpMyAdmin console.

CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL,
  `name` varchar(150) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Build File Upload Form

In the node project, make an index.html file; in this file, we will set up a simple form using the bootstrap 5. Therefore, insert the given HTML code into the file.

<!DOCTYPE html>
<html lang="en">
  <head>
    <title>Node js Store Excel File Records to MySQL Database</title>
    <meta charset="utf-8" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
  </head>
  <body>
    <div class="container mt-4">
      <form
        action="/upload-excel-file"
        method="post"
        enctype="multipart/form-data"
      >
        <input
          type="file"
          name="upload-excel-file"
          accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
        />
        <button
          type="submit"
          class="btn btn-primary"
          value="Upload File"
        ></button>
      </form>
    </div>
  </body>
</html>

Configure Node Server

In the node server file, we are setting up a database connection using MySQL module, using multer to upload excel file to mysql, creating a route to process excel file import to mysql database.

Open the app.js file and add the following code into the file.

const express = require('express')
const path = require('path')
const fs = require('fs')
const bodyparser = require('body-parser')

const readXlsxFile = require('read-excel-file/node')

const multer = require('multer')
const mysql = require('mysql')

const app = express()

app.use(express.static('./public'))

app.use(bodyparser.json())
app.use(
  bodyparser.urlencoded({
    extended: true,
  }),
)

const database = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'demo',
})

database.connect(function (err) {
  if (err) {
    return console.error('error: ' + err.message)
  }
  console.log('MySQL db connected.')
})

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 })

app.get('/', (req, res) => {
  res.sendFile(__dirname + '/index.html')
})

app.post(
  '/upload-excel-file',
  upload.single('upload-excel-file'),
  (req, res) => {
    uploadExceclFile(__basedir + '/uploads/' + req.file.filename)
    console.log(res)
  },
)

function uploadExceclFile(filePath) {
  readXlsxFile(filePath).then((rows) => {
    console.log(rows)

    rows.shift()
    database.connect((error) => {
      if (error) {
        console.error(error)
      } else {
        let query = 'INSERT INTO employee (id, name, age, address) VALUES ?'
        connection.query(query, [rows], (error, response) => {
          console.log(error || response)
        })
      }
    })
  })
}

let nodeServer = app.listen(8080, function () {
  let port = nodeServer.address().port
  let host = nodeServer.address().address
  console.log('Node connected at: ', host, port)
})

Run Node Server

Let’s start the node server with given command.

nodemon

For checking the app, you have to use this url that should be put on the browser’s address bar:

http://127.0.0.1:4000

How to Upload Excel to MySQL Database in Node JS

Summary

Throughout this tutorial, we learned how to flawlessly create node js excel file upload to MySQL database functionality from scratch.

Furthermore, we have explained how to set up a node application, how to create route and read excel file and store in the database using third-party packages.