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 modulesnpm 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
.
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
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…
- 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
andxlsx
modules installed properly. - Connect to the MySQL database.
- Read the Excel file.
- 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
- MySQL – NPM
- SheetJS – NPM
- Import & Export CSV With NodeJS MySQL – Code Boxx
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!