Node js MySQL import CSV file to database; In this tutorial, you will learn how to upload or import csv file data in MySQL database in Node js + Express using REST APIS + multer + fast CSV.
This tutorial will create a simple Rest API in node js; This Rest API is used to get CSV file data on the node js route. In the router method; reading this CSV file and then data will be imported/inserted/uploaded to the MySQL database.
How to import csv file data to mysql database using upload rest api in Node js Express
Let’s follow the following steps to import or upload csv file data to MySQL database using rest API, multi and fast CSV in node js express:
- Step 1 – Create Node Express js App
- Step 2 – Create Table in MySQL Database
- Step 3 – Install express body-parser mysql dependencies
- Step 4 – Create Server.js File and Route
- Step 5 – Start App Server
- Step 6 – Call Rest API with CSV File
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 express multer body-parser mysql dependencies
Execute the following command on the terminal to express multer ejs body-parser mysql dependencies :
npm install express multer body-parser mysql fast-csv
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.
fast-CSV – Fast-csv is library for parsing and formatting CSVs or any other delimited value file in node.
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 Server.js File and Route
Create server.js file; Then import express multer body-parser mysql fast csv dependencies in server.js and as well as create API route for import csv file data in MySql database; as shown below:
const express = require('express')
const app = express()
const bodyparser = require('body-parser')
const fs = require('fs');
const csv = require('fast-csv');
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.');
})
//! Use of Multer
var storage = multer.diskStorage({
destination: (req, file, callBack) => {
callBack(null, './uploads/')
},
filename: (req, file, callBack) => {
callBack(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname))
}
})
var upload = multer({
storage: storage
});
//! Routes start
//route for Home page
app.get('/', (req, res) => {
res.sendFile(__dirname + '/index.html');
});
//@type POST
//route for post data
// -> Express Upload RestAPIs
app.post('/api/uploadfile', upload.single("uploadfile"), (req, res) =>{
UploadCsvDataToMySQL(__dirname + '/uploads/' + req.file.filename);
res.json({
'msg': 'File uploaded/import successfully!', 'file': req.file
});
});
function UploadCsvDataToMySQL(filePath){
let stream = fs.createReadStream(filePath);
let csvData = [];
let csvStream = csv
.parse()
.on("data", function (data) {
csvData.push(data);
})
.on("end", function () {
// Remove Header ROW
csvData.shift();
// Open the MySQL connection
db.connect((error) => {
if (error) {
console.error(error);
} else {
let query = 'INSERT INTO customer (id, address, name, age) VALUES ?';
db.query(query, [csvData], (error, response) => {
console.log(error || response);
});
}
});
// delete file after saving to MySQL database
// -> you can comment the statement to see the uploaded CSV file.
fs.unlinkSync(filePath)
});
stream.pipe(csvStream);
}
//create connection
const PORT = process.env.PORT || 3000
app.listen(PORT, () => console.log(`Server is running at port ${PORT}`))
Step 5 – Start App Server
You can use the following command to start node js upload csv file to mysql database app server:
//run the below command npm start
Step 6 – Call Rest API with CSV File
Open your postman app and call country state city list apis; as shown below:
Countries List Api
URL :- http://localhost:3000/api/uploadfile Method :- POST
Important note :- Call the above api with Body “application/x-www-form-urlencoded”.
The “application/x-www-form-urlencoded” is the default encoding (in HTTP terms Content-Type) a web form uses to transfer data, not multipart/form-data. To send an HTTP post request on form submission with a Content Type of multipart/form-data, one must explicitly specify this as the enctype value.
Conclusion
Node js + REST Api + MySQL import CSV file to database; In this tutorial, you have learned how to upload import csv file data in MySQL database using Node js + Express + REST APIS + MySQL + express + multer + fast CSV.
