Node.js Express Project to Upload CSV File Data in MySQL Table Using fast-csv Library in Javascript

Welcome folks today in this blog post we will be uploading csv file and import data in mysql table using fast-csv library in javascript. All the full source code of the application is shown below.

Get Started

In order to get started you need to make a node.js project using the below command

npm init -y

Now we need to install the libraries using the npm command as shown below

npm i express

npm i multer

npm i fast-csv

npm i mysql

After that you need to create the below directory structure of the final app

As you can see we need to create the uploads directory where we will be storing all the files uploaded by the user and then we need to create the index.js file which will be the starting point of the express app

Creating the Table

After you install this library you need to start the xammp control panel and start the apache server and mysql server as well

Now you need to create the table inside the phpmyadmin as shown below

As you can see we have the four fields inside the user table. ID is the primary key of the table and then we are storing the name,age and country of the user.

index.js

const express = require('express')
const bodyparser = require('body-parser')
const fs = require('fs');
const path = require('path')
const mysql = require('mysql')
const multer = require('multer')
const csv = require('fast-csv');
 
const app = express()
app.use(express.static("./public"))
 
app.use(bodyparser.json())
app.use(bodyparser.urlencoded({
    extended: true
}))
 
// Database connection
const pool = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "",
    database: "exceldb"
})

const PORT = process.env.PORT || 5000
app.listen(PORT, () => console.log(`Node app serving on port: ${PORT}`))

const express = require('express')

const bodyparser = require('body-parser')

const fs = require('fs');

const path = require('path')

const mysql = require('mysql')

const multer = require('multer')

const csv = require('fast-csv');

const app = express()

app.use(express.static("./public"))

app.use(bodyparser.json())

app.use(bodyparser.urlencoded({

extended: true

}))

// Database connection

const pool = mysql.createPool({

host: "localhost",

user: "root",

password: "",

database: "exceldb"

})

const PORT = process.env.PORT || 5000

app.listen(PORT, () => console.log(`Node app serving on port: ${PORT}`))

As you can see we are importing all the libraries at the very top and then we are including the different middlewares required for this express app. And then we are creating the database connection using the createPool() method and then inside it we are providing the config details.

Initializing Multer

Now we will be initializing the multer library and declaring the storage object where we will be providing where we will be storing the files and also the filename of the uploaded file

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
});

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

});

Now guys we need to show the index.html file when the user goes to the / route as shown below

app.get('/', (req, res) => {
  res.sendFile(__dirname + '/index.html');
});

app.get('/', (req, res) => {

res.sendFile(__dirname + '/index.html');

});

index.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <title>Node CSV File Upload</title>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
  </head>
  <body>
    <h2 class="mb-3">Node Import CSV File to MySQL database Example</h2>
    <form action="/import-csv" method="post" enctype="multipart/form-data">
      <div class="mb-3">
        <input
          type="file"
          class="form-control"
          name="import-csv"
          accept="csv"
        />
      </div>
      <div class="d-grid">
        <input type="submit" class="btn btn-dark" value="Store File" />
      </div>
    </form>
  </body>
</html>

<!DOCTYPE html>

<head>

<title>Node CSV File Upload</title>

<link

href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"

rel="stylesheet"

</head>

<body>

<h2 class="mb-3">Node Import CSV File to MySQL database Example</h2>

<input

type="file"

class="form-control"

name="import-csv"

accept="csv"

</div>

</div>

</form>

</body>

</html>

As you can see we have the simple html5 form inside which we have the input field where we allow the user to upload the csv file and then we have the button to submit the form. After submitting this will make a post request to the /import-csv route. So now we need to define the route as shown below

app.post('/import-csv', upload.single("import-csv"), (req, res) =>{
    console.log(req.file.path)
    uploadCsv(__dirname + '/uploads/' + req.file.filename);
    res.send("data imported")
});

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

console.log(req.file.path)

uploadCsv(__dirname + '/uploads/' + req.file.filename);

res.send("data imported")

});

As you can see we are passing the multer middleware in the post request to first of all upload the csv file into the uploads directory and then we are calling the uploadCsv() function to actually parse the csv file using the fast-csv module and then insert the data into table using mysql module. And in the argument we are sending the full uploaded path of the csv file. Now we need to define that function as shown below

function uploadCsv(uriFile){
    let stream = fs.createReadStream(uriFile);
    let csvDataColl = [];
    let fileStream = csv
        .parse()
        .on("data", function (data) {
            csvDataColl.push(data);
        })
        .on("end", function () {
            csvDataColl.shift();
            
            pool.getConnection((error,connection) => {
                if (error) {
                    console.error(error);
                } else {
                    let query = 'INSERT INTO users (Name,Age,Country) VALUES ?';
                    connection.query(query, [csvDataColl], (error, res) => {
                        console.log(error || res);
                    });
                }
            });

            fs.unlinkSync(uriFile)
            
        });
  
    stream.pipe(fileStream);
}

function uploadCsv(uriFile){

let stream = fs.createReadStream(uriFile);

let csvDataColl = [];

let fileStream = csv

.parse()

.on("data", function (data) {

csvDataColl.push(data);

})

.on("end", function () {

csvDataColl.shift();

pool.getConnection((error,connection) => {

if (error) {

console.error(error);

} else {

let query = 'INSERT INTO users (Name,Age,Country) VALUES ?';

connection.query(query, [csvDataColl], (error, res) => {

console.log(error || res);

});

}

});

fs.unlinkSync(uriFile)

});

stream.pipe(fileStream);

}

As you can see we are using the parse() method to take the stream of the csv file and then we are removing the first row from the csv file because that will be the header row and after that we are inserting each row to the mysql table called users and inside it we have three columns name,age and gender. And here we are using the insert statement in mysql syntax to insert the data. After successfully insertion has taken place we are deleting the uploaded file.

Now you need to create the file.csv which will hold sample data records as shown below

file.csv

Name,Age,Country
Gautam,34,India
John,24,New Zealand
Harris,25,USA
Harry,56,England

Name,Age,Country

Gautam,34,India

John,24,New Zealand

Harris,25,USA

Harry,56,England