30-Dec-2022
.
Admin
Hello Friends,
In this example, I will show you how to upload CSV files using node js in MySQL. I explained simply step by step how to upload data from a .csv file into a MySQL table using node.js. let’s discuss node js MySQL CSV file upload rest API tutorial. I would like to show you upload CSV data into MySQL using node.js.
You will learn node js MySQL CSV file upload rest API tutorial. you can understand the concept of uploading CSV data into MySQL using node.js. you will do the following things to upload large CSV files into MySQL quicker than load data in the file.
Step 1: Create Node Express js App
Execute the following command on the terminal to create the 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 in 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 makes it available under req.body property. In other words, it simplifies incoming requests.
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 the 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 an index.html file and add the following code into it:
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to Upload CSV File using Node js in MySQL? - Nicesnippets.com</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<h1>How to Upload CSV File using Node js in MySQL? - Nicesnippets.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 the form method should be posted.
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 running this command open your browser and hit
http://127.0.0.1:3000/
I hope it can help you...
#Node JS
