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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

 

QUICK NOTES

• Download and unzip into your project folder. For example, D:\importxls.
• Navigate to the project folder cd D:\importxls, and install the required modules npm install mysql xlsx
• Import 1-users.sql into the database.
• Change the database settings to your own in 3-import.js.
• Launch – node 3-import.js.

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

 

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 nor 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