Node.js Project to Import Excel Sheet Data into MySQL Database Using xlsx Module in Javascript

Welcome folks today in this blog post we will be importing excel sheet data into mysql database using xlsx module in node.js using javascript. All the full source code of the application is shown below.

Get Started

In order to get started you need to make a new node.js project using the below command as shown below

npm init -y

This will create the package.json file for the node.js project and now you need to install the below libraries using the npm command

npm i xlsx

Now first of all you need to create the table inside the mysql database using the sql code as shown below

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=latin1;
 
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;

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=latin1;

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;

As you can see we are making the users table inside it we have three columns. Id is the primary key and then we have two other columns which is the user_name and user_email.

After that you need to make an index.js file and copy paste the following code

index.js

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: "exceltomysql",
});

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: "exceltomysql",

});

As you can see we are importing the mysql driver library in node.js and also we are importing the xlsx library to read the content of the excel file and we are using the createConnection() method to connect to the mysql database and here we are passing the hostname,user and password and also we are passing the database name also to connect to it.

Creating the Excel file

Now you can use any excel sheet software called google sheets online to insert some data in the excel file and download it as excel file

Now you can insert the excel file inside the mysql database and table using the node.js code as shown below

// (C) OPEN EXCEL FILE - USE FIRST WORKSHEET
var workbook = xlsx.readFile("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();

// (C) OPEN EXCEL FILE - USE FIRST WORKSHEET

var workbook = xlsx.readFile("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();

Now inside the above code we are looping through all the records which are present inside the users.xlsx file and then we are using the sql statement to insert the records into the mysql table. Now if you execute the node.js app you will see the excel data imported into the mysql table

node index.js