Upload CSV file in mysql database using node js; This tutorial will show you how to upload CSV file data into MySQL database using Node js + express + multer + fast CSV.
This tutorial will create a simple CSV file upload form; This form will send the CSV file data to the node js route. In the router method; reading this CSV file, the data will be inserted/uploaded to the MySQL database.
How to Upload CSV file data in MySQL database using Node. js + Express+ Fast csv + multer
- 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 CSV 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 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 CSV File Upload Form
Create a form with a `file input` element that allows us to choose the csv 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 csv 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 csv file to MySQL database - Tutsmake.com</h1>
<form action="/uploadfile" enctype="multipart/form-data" method="post">
<input type="file" name="uploadfile" accept='csv' >
<input type="submit" value="Upload CSV">
</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 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
// upload csv to database
app.post('/uploadfile', upload.single("uploadfile"), (req, res) =>{
UploadCsvDataToMySQL(__dirname + '/uploads/' + req.file.filename);
console.log('CSV file data has been uploaded in mysql database ' + err);
});
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 6 – 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 after run this command open your browser and hit http://127.0.0.1:3000/
Conclusion
Upload CSV file in mysql database using node js; In this tutorial, you have learned how to upload CSV file data into MySQL database using Node js + express + multer + fast CSV.
