Node JS Upload Excel in MySQL Database Tutorial

Author: Digamber Singh | Published: May 22, 2022 | Updated: November 13, 2023 | Category: Node

Node Import Excel file to MySQL database example; In this short and descriptive guide, we will teach how to upload Excel file data to the MySQL database using the Node js application.

To import the excel records into the MySQL database, we will take the help of essential npm packages. Those packages are read-excel-file, express, multer, body-parser, and mysql.

We will create the basic node app using the npm init command, then install the important libraries using the command-line tool. The MySQL package allows us to connect the node app to the MySQL database.

We will create the primary routes or URL using the express middleware and configure the excel file upload functionality using the multer client.

We will use the latest version of the Bootstrap library to build the excel file upload form in Node.

How to Import Excel File Records in MySQL Database via Node Js using Multer

Here are the nuances that we need to follow the given instructions to build the feature:

Step 1: Create Project Folder
Step 2: Set Up Package JSON
Step 2: Install NPM Packages
Step 3: Generate MySQL Table
Step 4: Create Bootstrap Upload Form
Step 5: Make Server File
Step 6: Upload Excel to MySQL

Create Project Folder

Before you start, install Node and NPM on your development machine.

Let’s create a blank folder, use the provided command to make one:

mkdir node-demo

Step inside the folder with provided command:

cd node-demo

Set Up Package JSON

Generate the package.json file which will retain the project information, modules and version information.

We would like you to run the provided command:

npm init

Create the app.js file, in this file we will write the node server configuration code.

Make sure to add the server file name in the package.json file’s scripts section.

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

Install NPM Packages

On the command-prompt, provided command has to be added, ensure that you evoke the command to install the npm module.

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

Express: This minimal and flexible server framework helps build server-side logic for modern web applications.

Read Excel File: Identify excel files, parse excel to JSON and reads excel small to medium *.xlsx files in node js or browser.

Multer: A robust node js middleware that handles multipart/form-data, pretty smoothly, majory used for uploading files.

MySQL: This tiny plugin bridges the gap between your node app and MySQL database.

Body Parser: A node js middleware that helps parse the incoming request before you handle them.

Generate MySQL Table

In order to insert the excel data into the MySql database, we require a table in the database.

Here is how you can generate the MySQL table using the provided SQL query.

CREATE TABLE `user` (
  `id` bigint(50) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Create File Import Form

Inside your node project, create a new file name it index.html. Then the given code must go into the index.html file.

<!DOCTYPE html>
<html lang="en">
  <head>
    <title>Node js Save Excel Data in MySQL Database Example</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">
      <form action="/import-excel" enctype="multipart/form-data" method="post">
        <input
          type="file"
          name="import-excel"
          accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
        />
        <button type="submit" class="btn btn-dark" value="Store File"></button>
      </form>
    </div>
  </body>
</html>

Define the HTML form tags, set the action, and pass the import-excel route to the action tag.

Create an input field, set the name and accept properties, and eventually add the button which will send the excel file to the MySQL database when clicked.

Make Server File

For setting up node server open the app.js file, in this file add the provided code.

To store the excel file, create the 'uploads' folder in your node app.

const express = require('express')
const fs = require('fs')
const path = require('path')
const bodyparser = require('body-parser')
const readXlsxFile = require('read-excel-file/node')
const mysql = require('mysql')
const multer = require('multer')
const app = express()
app.use(express.static('./public'))
app.use(bodyparser.json())
app.use(
  bodyparser.urlencoded({
    extended: true,
  }),
)
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('Database 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 uploadFile = multer({ storage: storage })
app.get('/', (req, res) => {
  res.sendFile(__dirname + '/index.html')
})
app.post('/import-excel', uploadFile.single('import-excel'), (req, res) => {
  importFileToDb(__basedir + '/uploads/' + req.file.filename)
  console.log(res)
})
function importFileToDb(exFile) {
  readXlsxFile(exFile).then((rows) => {
    rows.shift()
    database.connect((error) => {
      if (error) {
        console.error(error)
      } else {
        let query = 'INSERT INTO user (id, name, email) VALUES ?'
        connection.query(query, [rows], (error, response) => {
          console.log(error || response)
        })
      }
    })
  })
}
let nodeServer = app.listen(4000, function () {
  let port = nodeServer.address().port
  let host = nodeServer.address().address
  console.log('App working on: ', host, port)
})

In this file, we define the code for making the database connection with node.

At the same time we added the multer logic to store the excel file in MySQL database using the express routes.

Upload Excel to MySQL

Type the provided command, and run the command to run the node server.

npm start

You can use the provided url to run the application.

http://127.0.0.1:4000

Node JS Upload Excel in MySQL Database Tutorial

Conclusion

In this definitive guide, we revealed the process of uploading excel file data to the MySql database through the Node js platform using the external dependencies.

We showed you by example how to build a node js app from scratch, how to set up a node server, and configure the file upload functionality using the multer and express modules.

We throughout believe you will indeed like this tutorial.

Loved this? Share it with others:

Digamber Singh

An experienced full-stack developer with a passion for providing top-notch web experiences, proficient in developing both the front and back ends of a web application by utilizing the expertise of HTML, CSS, JavaScript, PHP, and Python.

Follow me: