1. Install the required packages:
npm install express mysql csv-parse
2. Create a file called ‘import.js’ and add the following code:
const express = require(‘express’);
const mysql = require(‘mysql’);
const csv = require(‘csv-parse’);
const app = express();
// Create connection
const db = mysql.createConnection({
host : ‘localhost’,
user : ‘root’,
password : ”,
database : ‘mydb’
});
// Connect
db.connect((err) => {
if(err){
throw err;
}
console.log(‘MySql Connected…’);
});
// Create table
app.get(‘/createdb’, (req, res) => {
let sql = ‘CREATE TABLE data(id int AUTO_INCREMENT, name VARCHAR(255), address VARCHAR(255), PRIMARY KEY(id))’;
db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send(‘Table created…’);
});
});
// Import data
app.get(‘/import’, (req, res) => {
let sql = ‘LOAD DATA LOCAL INFILE “data.csv” INTO TABLE data FIELDS TERMINATED BY “,” LINES TERMINATED BY “\n” (name, address)’;
db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send(‘Data imported…’);
});
});
// Select data
app.get(‘/select’, (req, res) => {
let sql = ‘SELECT * FROM data’;
db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send(‘Data selected…’);
});
});
// Parse CSV
app.get(‘/parse’, (req, res) => {
csv()
.fromFile(‘data.csv’)
.on(‘json’,(jsonObj)=>{
// combine csv header row and csv line to a json object
// jsonObj.a ==> 1 or 4
})
.on(‘done’,(error)=>{
console.log(‘end’)
})
});
app.listen(‘3000’, () => {
console.log(‘Server started on port 3000’);
});
Import/upload/insert excel file data to MySQL database using node js + express; In this tutorial, you will learn how to upload/Import/insert Excel file data to MySQL database in Node js express using multer and read-excel-file.
Read, import and upload an excel file is a common task in the node js app. So in this tutorial, you will learn how to upload an excel file in the node js app and how to import excel file data into the MySQL database.
How to Import/Upload Excel File Data to MySQL Database in Node Js express using Multer
Let’s follow the following steps to import/upload/insert excel file data to MySQL database in node js express using multer:
- Step 1 – Create Node Express js App
- Step 2 – Create Table in MySQL Database
- Step 3 – Install Required Packages
- Step 4 – Create Excel File Upload Form
- Step 5 – Create Server.js File
- Step 6 – Start App Server
Step 1 – Create Node Express js App
Execute the following command on terminal to create node js app:
mkdir my-app cd my-app npm init -y
Step 2 – Create Table in MySQL Database
Execute the following sql query to create a table into your database:
CREATE TABLE `customer` ( `id` bigint(20) NOT NULL, `address` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
Step 3 – Install Required Packages
Execute the following command on the terminal to express multer, ejs, body-parser, read-execel-file, MySQL dependencies :
npm install express multer body-parser mysql read-excel-file
body-parser – Node.js request body parsing middleware which parses the incoming request body before your handlers, and make it available under req.body property. In other words, it simplifies the incoming request.
Read-Excel-File – Read small to medium `*.xlsx` files in a browser or Node.js. Parse to JSON with a strict. import readXlsxFile from ‘read–excel–file‘.
Multer – Multer is a node.js middleware for handling multipart/form-data , which is primarily used for uploading files. It is written on top of busboy for maximum efficiency.
MySQL – A node.js driver for MySQL. It is written in JavaScript, does not require compiling, and is 100% MIT licensed.
Step 4 – Create Excel File Upload Form
Create a form with a `file input` element that allows us to choose the Excel file and a button to submit the form; So create index.html file and add the following code into it:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Node js upload/Import excel file to MySQL database - Tutsmake.com</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<h1>Node js upload Excel file to MySQL database - Tutsmake.com</h1>
<form action="/uploadfile" enctype="multipart/form-data" method="post">
<input type="file" name="uploadfile" accept='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel' >
<input type="submit" value="Upload Excel">
</form>
</body>
</html>
Make sure your form must have enctype="multipart/form-data"attribute and form method should be post.
Step 5 – Create Server.js File
Create server.js file and import express multer body-parser mysql dependencies in server.js; as shown below:
const express = require('express')
const app = express()
const bodyparser = require('body-parser')
const fs = require('fs');
const readXlsxFile = require('read-excel-file/node');
const mysql = require('mysql')
const multer = require('multer')
const path = require('path')
//use express static folder
app.use(express.static("./public"))
// body-parser middleware use
app.use(bodyparser.json())
app.use(bodyparser.urlencoded({
extended: true
}))
// Database connection
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "test"
})
db.connect(function (err) {
if (err) {
return console.error('error: ' + err.message);
}
console.log('Connected to the MySQL server.');
})
// Multer Upload Storage
const storage = multer.diskStorage({
destination: (req, file, cb) => {
cb(null, __basedir + '/uploads/')
},
filename: (req, file, cb) => {
cb(null, file.fieldname + "-" + Date.now() + "-" + file.originalname)
}
});
const upload = multer({storage: storage});
//! Routes start
//route for Home page
app.get('/', (req, res) => {
res.sendFile(__dirname + '/index.html');
});
// -> Express Upload RestAPIs
app.post('/uploadfile', upload.single("uploadfile"), (req, res) =>{
importExcelData2MySQL(__basedir + '/uploads/' + req.file.filename);
console.log(res);
});
// -> Import Excel Data to MySQL database
function importExcelData2MySQL(filePath){
// File path.
readXlsxFile(filePath).then((rows) => {
// `rows` is an array of rows
// each row being an array of cells.
console.log(rows);
/**
[ [ 'Id', 'Name', 'Address', 'Age' ],
[ 1, 'john Smith', 'London', 25 ],
[ 2, 'Ahman Johnson', 'New York', 26 ]
*/
// Remove Header ROW
rows.shift();
// Open the MySQL connection
db.connect((error) => {
if (error) {
console.error(error);
} else {
let query = 'INSERT INTO customer (id, address, name, age) VALUES ?';
connection.query(query, [rows], (error, response) => {
console.log(error || response);
/**
OkPacket {
fieldCount: 0,
affectedRows: 5,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '&Records: 5 Duplicates: 0 Warnings: 0',
protocol41: true,
changedRows: 0 }
*/
});
}
});
})
}
// Create a Server
let server = app.listen(8080, function () {
let host = server.address().address
let port = server.address().port
console.log("App listening at http://%s:%s", host, port)
})
Step 6 – Start App Server
You can use the following command to start node js upload/import Excel file to MySQL database app server:
//run the below command npm start after run this command open your browser and hit http://127.0.0.1:3000/
Conclusion
Upload/Import Excel file in MySQL database using node js; In this tutorial, you have learned how to upload/import Excel file data into MySQL database using Node js + express + multer + read-excel-file.
