Import Excel Into Database With NodeJS MySQL (Simple Example)

Welcome to a tutorial on how to import an Excel spreadsheet with MySQL and NodeJS. So want to “put an Excel file into the database”? Well, it’s actually a simple process of “read the Excel file” and “insert into database”. Read on for the example!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

QUICK SETUP

Run npm i mysql xlsx to install the required modules.

 

 

PART 1) DUMMY USER TABLE

CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_name` (`user_name`);
 
ALTER TABLE `users`
  MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

First, let us start with the database, by creating a very simple dummy users table to work with.

• user_id The user ID, primary key and auto-increment.
• user_name User’s name.
• user_email User’s email address.

 

 

PART 2) EXCEL FILE

Jo Doe	jo@doe.com
Job Doe	job@doe.com
Joe Doe	joe@doe.com
Jog Doe	jog@doe.com
Joh Doe	joh@doe.com
Joi Doe	joi@doe.com
Jol Doe	jol@doe.com
Jon Doe	jon@doe.com
Jou doe	jou@doe.com
Joy Doe	joy@doe.com

Next, I don’t think this needs any explanation. Just a bunch of users that we will import into the database.

 

 

PART 3) NODEJS IMPORT EXCEL

// (A) LOAD MODULES
// npm install mysql xlsx
// https://www.npmjs.com/package/mysql
// https://www.npmjs.com/package/xlsx
const mysql = require("mysql"),
      xlsx = require("xlsx");
 
// (B) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "test"
});
 
// (C) OPEN EXCEL FILE - USE FIRST WORKSHEET
var workbook = xlsx.readFile("2-users.xlsx"),
    worksheet = workbook.Sheets[workbook.SheetNames[0]],
    range = xlsx.utils.decode_range(worksheet["!ref"]);
 
// (D) IMPORT EXCEL
for (let row=range.s.r; row<=range.e.r; row++) {
  // (D1) READ CELLS
  let data = [];
  for (let col=range.s.c; col<=range.e.c; col++) {
    let cell = worksheet[xlsx.utils.encode_cell({r:row, c:col})];
    data.push(cell.v);
  }
 
  // (D2) INSERT INTO DATABASE
  let sql = "INSERT INTO `users` (`user_name`, `user_email`) VALUES (?,?)";
  db.query(sql, data, (err, results, fields) => {
    if (err) { return console.error(err.message); }
    console.log("USER ID:" + results.insertId);
  });
}
 
// (E) DONE - CLOSE DB CONNECTION
db.end();

Lastly, I don’t think this needs a line-by-line explanation either…

1. Import/load all the required modules. Yep, NodeJS does not have the native capabilities to directly connect to MySQL or read Excel files. Just make sure that you have both mysql and xlsx modules installed properly.
2. Connect to the MySQL database.
3. Read the Excel file.
4. Read the cells, and insert them into the table.

 

 

 

IMPORT EFFICIENCY

While the above example works, take note of how it processes row-by-row. That is actually very inefficient when dealing with large imports. So a small homework for those who are dealing with massive Excel files – Change that section to import multiple rows at once, that is, INSERT INTO `users` (`user_name`, `user_email`) VALUES (?,?), (?,?), (?,?), ...

 

ERROR CHECKING

Also, there is no error checking. We assume that there will always be 2 columns in the Excel file, in the correct order. So yep, you will want to add some “foolproofing checks” in your own project.

 

LINKS & REFERENCES

• MySQL – NPM
• SheetJS – NPM
• Import & Export CSV With NodeJS MySQL – Code Boxx