In this tutorial, you will learn how to upload csv file into Mysql Database using Multer and CSV Parser in Node js.

To upload csv file into Mysql Database, We will create API like /upload-csv and then we will hit by postman and upload it into Mysql Database.

How to upload csv file into Mysql Database using Express in Node Js.

Follow the steps below to upload, import, or insert data from a CSV file into a MySQL database using node js express, multer, and a csv parser.

• Step 1 – Create Node App
• Step 2 – Create Table in MySQL Database
• Step 3 – Install express multer mysql dependencies
• Step 4 – Create index.js File
• Step 5 – Start App Server

Step 1 – Create Node App

mkdir demo-app
cd demo-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 `users` (
  `id` bigint(20) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` json DEFAULT NULL,
  `gender` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Step 3 – Install dependencies

Execute the following command on the terminal to express multer csv parser mysql dependencies :

npm install express multer fs mysql csv-parser path

Step 4 – Create index.js File

const express = require("express");
const mysql = require("mysql");
const multer =  require("multer");
const app = express();
const path = require("path");
const fs = require("fs");
const csv = require("csv-parser");

// Create database connection
const db = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "root",
    database: "luvcondingcsv"
})

db.connect(function (err) {
    if (err) {
        return console.error('error: ' + err.message);
    }
    console.log('Connected to the MySQL server.');
});

// Upload csv file by using 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
});

app.post("/upload-csv", upload.single("filename") ,  (req,res) => {

    const importedFilepath = req.file.path;
    
    let csvOption = {
        mapHeaders: ({ header, index }) => header.trim()
    };

    const results = [];
    const stream =   fs.createReadStream(importedFilepath).pipe(csv(csvOption));

    stream.on('headers', async (headers) => {});

    stream.on('data', (eachData) => {
        results.push(eachData);
    });

    stream.on('end', () => {
       
        let query = `INSERT INTO users (name, age, address, gender) VALUES ?`;
        db.query(query, [results] ,(error, response) => {
            //console.log(error || response);
        });

        
        fs.unlinkSync(importedFilepath);

        return res.send({
            status: "success",
            message: "CSV data uploaded successfully",
        });
    });

    stream.on('error', err => {
        // This executes when some error happens in csv-parser execution
        return res.status(500).send({
            status: "error",
            message: err,
        });
    });

});



//create connection
const PORT = process.env.PORT || 3000
app.listen(PORT, () => console.log(`Server is running at port ${PORT}`))

Step 5 – Start App Server

Run the below command

node index.js

if you want to use npm start then add the following line in scripts in package.json

Once server is started , open postman and hit POST request

http://localhost:3000/upload-csv

Conclusion

Use Node.js to upload a CSV file to a MySQL database. In this lesson, you learned how to use Node.js, express, multer, and the CSV Parser to upload CSV file data to the MySQL database.