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

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

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.

 

 

IMPORT EXCEL WITH NODEJS MYSQL

All right, let us now get into the example of importing an Excel file into MySQL.

 

PART 1) DUMMY USER TABLE

1-users.sql
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

2-users.xlsx
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

3-import.js
// (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.

 

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

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

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *