Node.js: Upload/Import Excel file data into Database

The Excel file is a spreadsheet file format created by Microsoft for use with Microsoft Excel. You can use the file to create, view, edit, analyse data, charts, budgets and more. In this tutorial, I will show you how to upload/import Excel file data into MySQL Database using Node.js & read-excel-file.

Related Posts:
– Upload/store images in MySQL using Node.js, Express & Multer
– How to upload/store images in MongoDB using Node.js, Express & Multer
– Import CSV data into MySQL using Node.js
– Import CSV data into PostgreSQL using Node.js
– Import CSV file into MongoDB collection using Node.js

CSV file instead:
Node.js: Upload CSV file data into Database with Express

Node.js Rest APIs for uploading Excel Files

Assume that we have an .xlsx file that contains Tutorial data in Tutorials sheet as following:

We’re gonna create a Node.js Application that provides APIs for:

• uploading Excel File to the Node.js Express Server & storing data in MySQL Database
• getting list of items from MySQL table
• downloading MySQL table data as Excel file

After the Excel file is uploaded successfully, tutorials table in MySQL database will look like this:

If we get list of Tutorials, the Node.js Rest Apis will return:

Node.js Rest API returns Excel File

If you send request to /api/excel/download, the server will return a response with an Excel file tutorials.xlsx that contains data in MySQL table:

How to do this?
You need to set the HTTP header:

"Content-disposition" : "attachment; filename=[yourFileName]" 
"Content-Type" : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

You can find step by step for downloading Excel file in the tutorial:
Node.js Download Excel file example with exceljs

These are APIs to be exported:

Technology

• express 4.17.1
• multer 1.4.2
• mysql2 2.1.0
• read-excel-file 4.0.6
• sequelize 5.21.13

Project Structure

This is the project directory that we’re gonna build:

– db.config.js exports configuring parameters for MySQL connection & Sequelize.
– models/index.js: uses configuration above to initialize Sequelize, models/tutorial.model.js for Sequelize Tutorial data model.
– middleware/upload.js: initializes Multer Storage engine and defines middleware function to save Excel file in uploads folder.
– excel.controllers.js:

• use read-excel-file to read Excel file in uploads folder, then save data to MySQL database with Sequelize Model.
• export functions for retrieving all tutorials in database table

– routes/tutorial.routes.js: defines routes for endpoints that is called from HTTP Client, use controllers (along with middleware) to handle requests.
– server.js: initializes routes, runs Express app.

Setup Node.js Excel File Upload project

Open command prompt, change current directory to the root folder of our project.
Install Express, Multer, Sequelize, Mysql2 with the following command:

npm install express multer sequelize mysql2 read-excel-file

The package.json file will look like this:

{
  "name": "node-js-upload-download-excel-files",
  "version": "1.0.0",
  "description": "Node.js Upload/Import Excel files to MySQL Database and download Excel File",
  "main": "src/server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "node js",
    "upload",
    "import",
    "download",
    "excel",
    "files",
    "mysql",
    "database"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "multer": "^1.4.2",
    "mysql2": "^2.1.0",
    "read-excel-file": "^4.0.6",
    "sequelize": "^5.21.13"
  }
}

Configure MySQL database & Sequelize

In the src folder, we create a separate config folder for configuration with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for MySQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

• max: maximum number of connection in pool
• min: minimum number of connection in pool
• idle: maximum time, in milliseconds, that a connection can be idle before being released
• acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

Now we initialize Sequelize in src/models folder.

Create src/models/index.js with the following code:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);

module.exports = db;

We’re gonna define Tutorial model in the next step.

Define the Sequelize Model

In models folder, create tutorial.model.js file like this:

module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Tutorial;
};

This Sequelize Model represents tutorials table in MySQL database. These columns will be generated automatically: id, title, description, published, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

• create a new Tutorial: create(object)
• create multiple Tutorials: bulkCreate(objects)
• find a Tutorial by id: findByPk(id)
• get all Tutorials: findAll()
• update, remove Tutorials…

We’re gonna use bulkCreate() and findAll() in our Controller.

Create middleware for uploading & storing Excel file

Inside middleware folder, create upload.js file with the following code:

const multer = require("multer");

const excelFilter = (req, file, cb) => {
  if (
    file.mimetype.includes("excel") ||
    file.mimetype.includes("spreadsheetml")
  ) {
    cb(null, true);
  } else {
    cb("Please upload only excel file.", false);
  }
};

var storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, __basedir + "/resources/static/assets/uploads/");
  },
  filename: (req, file, cb) => {
    console.log(file.originalname);
    cb(null, `${Date.now()}-bezkoder-${file.originalname}`);
  },
});

var uploadFile = multer({ storage: storage, fileFilter: excelFilter });
module.exports = uploadFile;

In the code above, we’ve done these steps:
– First, we import multer module.
– Next, we configure multer to use Disk Storage engine.
– We also define a filter to only allow file with excel format.

You can see that we have two options here:
– destination determines folder to store the uploaded files.
– filename determines the name of the file inside the destination folder.
– We add the [timestamp]-bezkoder- prefix to the file’s original name to make sure that the duplicates never occur.

Create Controller for uploading/importing Excel file

controllers/tutorial/excel.controller.js

const db = require("../../models");
const Tutorial = db.tutorials;

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

const upload = async (req, res) => {
  try {
    if (req.file == undefined) {
      return res.status(400).send("Please upload an excel file!");
    }

    let path =
      __basedir + "/resources/static/assets/uploads/" + req.file.filename;

    readXlsxFile(path).then((rows) => {
      // skip header
      rows.shift();

      let tutorials = [];

      rows.forEach((row) => {
        let tutorial = {
          id: row[0],
          title: row[1],
          description: row[2],
          published: row[3],
        };

        tutorials.push(tutorial);
      });

      Tutorial.bulkCreate(tutorials)
        .then(() => {
          res.status(200).send({
            message: "Uploaded the file successfully: " + req.file.originalname,
          });
        })
        .catch((error) => {
          res.status(500).send({
            message: "Fail to import data into database!",
            error: error.message,
          });
        });
    });
  } catch (error) {
    console.log(error);
    res.status(500).send({
      message: "Could not upload the file: " + req.file.originalname,
    });
  }
};

const getTutorials = (req, res) => {
  Tutorial.findAll()
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials.",
      });
    });
};

module.exports = {
  upload,
  getTutorials,
};

Now look at the upload function:
– First we get and check file upload from req.file.
– Next we use read-excel-file to read Excel file in uploads folder, the data which is returned as rows will be changed to tutorials array.
– Then we use Sequelize model bulkCreate() method to save the tutorials array (id, title, description, published) to MySQL database.

The getTutorials() function uses findAll() method to return all Tutorials stored in the database tutorials table.

Define Routes for uploading Excel File

When a client sends request for an endpoint using HTTP request (POST Excel file, GET tutorials), we need to determine how the server will response by setting up the routes.

These are our routes:

• /api/excel/upload: POST
• /api/excel/tutorials: GET

Create a tutorial.routes.js inside routes folder with content like this:

const express = require("express");
const router = express.Router();
const excelController = require("../controllers/tutorials/excel.controller");
const upload = require("../middlewares/upload");

let routes = (app) => {
  router.post("/upload", upload.single("file"), excelController.upload);
  router.get("/tutorials", excelController.getTutorials);

  app.use("/api/excel", router);
};

module.exports = routes;

You can see that we use a controller from excel.controller.js.

Create Express app server

Finally, we create an Express server.

server.js

const express = require("express");
const app = express();
const db = require("./models");
const initRoutes = require("./routes/tutorial.routes");

global.__basedir = __dirname + "/..";

app.use(express.urlencoded({ extended: true }));
initRoutes(app);

db.sequelize.sync();
// db.sequelize.sync({ force: true }).then(() => {
//   console.log("Drop and re-sync db.");
// });

let port = 8080;
app.listen(port, () => {
  console.log(`Running at localhost:${port}`);
});

In the code above, we initialize Express Router and call Sequelize sync() method.

db.sequelize.sync();

In development, you may need to drop existing tables and re-sync database. Just use force: true as following code:

db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

Run & Check

First we need to create uploads folder with the path resources/static/assets.
On the project root folder, run this command: node src/server.js

Let’s use Postman to make HTTP POST request with an Excel file.

The result in MySQL tutorials table:

Conclusion

Today we’ve built a Rest CRUD API using Node.js Express to upload and import data from Excel file to Mysql database table.

We also see how to use read-excel-file to read data from Excel file, Sequelize to retrieve items in database table without need of boilerplate code.

If you want to add Pagination while getting data from MySQL table, you can find the instruction at:
Server side Pagination in Node.js with Sequelize & MySQL

For downloading Excel file:
Node.js Download Excel file example with exceljs

Or working with CSV file instead:
Node.js: Upload CSV file data into Database with Express

Happy learning! See you again.

Further Reading

• https://www.npmjs.com/package/express
• https://www.npmjs.com/package/multer
• https://sequelize.org/v3/api/model/
• read-excel-file

– Node.js Rest APIs with Express & MySQL
– Node.js Rest APIs with Express & MySQL (including Sequelize)

Fullstack:
– Vue.js + Node.js + Express + MySQL example
– Vue.js + Node.js + Express + MongoDB example
– Angular 8 + Node.js Express + MySQL example
– Angular 10 + Node.js Express + MySQL example
– Angular 11 + Node.js Express + MySQL example
– Angular 12 + Node.js Express + MySQL example
– React + Node.js + Express + MySQL example

Security: Node.js – JWT Authentication & Authorization example
Deployment:
– Deploying/Hosting Node.js app on Heroku with MySQL database
– Dockerize Node.js Express and MySQL example – Docker Compose

Node.js & MySQL Associations:
– One-to-Many Relationship example
– Many-to-Many Relationship example

Source Code

You can find the complete source code for this example on Github.