Node js Import CSV File Data to MySQL Database Tutorial

To import a CSV file into a MySQL database using Node.js REST API, you can use multer and fast csv. In this tutorial, you will learn how to upload CSV files and import data in MySQL database using Node js + Express rest apis with the postman.

How to Import CSV File Data into MySQL DB using Node Express JS REST APIS?

Steps to import or upload csv file data to MySQL database:

Step 1 – Create Node Express js App

Run the following command on cmd to create the node js app:

mkdir my-app
cd my-app
npm init -y

Step 2 – Create Table in MySQL Database

Run 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

Run the following command on the cmd to express multer ejs body-parser mysql dependencies :

npm install express multer body-parser mysql fast-csv

Step 4 – Create Server.js File and Route

Create server.js file, and import the express multer body-parser MySQL Fast CSV dependencies into it as well as create an API route to import the CSV file data into the MySQL database:

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 Application Server

Run the following command to start application server:

//run the below command

npm start

Step 6 – Test This Application

Open your postman application, and test this application for upload CSV file data to MySQL database:

Countries List Api

URL :- http://localhost:3000/api/uploadfile
Method :- POST

Important note:- Call the above API with Body “application/x-www-form-urlencoded”.

Conclusion

That’s it, you have learned how to upload import csv file data in MySQL database using Node js + Express + REST APIS + MySQL + express + multer + fast CSV.

Recommended Node JS Tutorials