Site icon RemoteStack

Node js Upload CSV File Records in MySQL Database Tutorial

In this comprehensive tutorial, we will learn how to upload CSV (Comma-separated values) file records in the MySQL database through the Node js application. To upload the CSV file data in Node, we will install, configure and use Express js, Multer, and Fast CSV modules.

We will start this guide by setting up a Node js project. Install fast csv, multer, and express js packages; we will build a CSV file upload form using these packages.

CSV is commonly known as comma-separated values; this file contains the data in comma-separated values. Every line in the CSV file holds the record or data. Each record may include one or more than one field, separated by commas.

We need this form to collect CSV file data from Express routes; using the same express routes, multer, and fast csv, we will store the CSV file data in the MySQL database.

Multer is a node middleware best used for handling multipart/form-data, which is primarily used for uploading files.

Whereas the Fast CSV is a CSV parser and formatter exclusively developed for node js. It is used for parsing and formatting CSVs or any other delimited value file in a node.

Here are some of the features:

How to Upload CSV File Data in MySQL Database via Node Js and Multer

Build Node Project

Create the new folder for storing the project related files using the following command.

mkdir node-proxima

Move inside the application folder:

cd node-proxima

Head over to command prompt type the given command for generating the package.json file.

npm init

Now, you need to create the app.js file, also don’t forget to add this file name into the package.json file.

{
  "main": "app.js",
}

Add Required Packages

Run the suggested command from the command prompt to install express fast-csv multer mysql body-parser nodemon packages.

npm install express fast-csv multer mysql body-parser nodemon

Create Table in MySQL

Head over to the SQL command section and run the suggested command to generate a new employee table.

CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `age` int(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE= InnoDB DEFAULT CHARSET=utf8

Build File Upload Form

In the project folder, you need to create the index.html file, also insert the suggested code into the file.

This will build the CSV file upload form using Bootstrap 5, and use the Bootstrap input and button element to create the csv file upload form for node.

<!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 CSV File Upload to MySQL database Example</h2>

    <form action="/csvfileupload" enctype="multipart/form-data" method="post">
      <input
        type="file"
        class="form-control"
        name="csvfileupload"
        accept="csv"
      />

      <input type="submit" class="btn btn-primary" value="Upload File" />
    </form>
  </body>
</html>

Make sure to add a multipart/form-data attribute on the form element.

The multipart/form-data is a form attribute, ideally attached to the form tag for file uploading purposes. The multipart refers to form data divided into multiple parts and send to the server.

Set Up Node Server

We now have to configure the node server, make sure to insert the given code into the app.js file.

const express = require('express')
const bodyparser = require('body-parser')

const fs = require('fs');
const mysql = require('mysql')
const csv = require('fast-csv');
const path = require('path')
const multer = require('multer')

 
const app = express()

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

app.use(bodyparser.json())
app.use(bodyparser.urlencoded({
    extended: true
}))
 
// Database connection
const database = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "demo"
})
 
database.connect(function (err) {
    if (err) {
        return console.error('error: ' + err.message);
    }
    console.log('Connected to the MySQL server.');
})
 

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.get('/', (req, res) => {
  res.sendFile(__dirname + '/index.html');
});
 

app.post('/csvfileupload', upload.single("csvfileupload"), (req, res) =>{
    handleCsvFile(__dirname + '/uploads/' + req.file.filename);
    console.log('File uploaded :' + err);
});
 
function handleCsvFile(filePath){
    let stream = fs.createReadStream(filePath);
    let csvRecordsArr = [];
    let csvDataStream = csv
        .parse()
        .on("data", function (data) {
            csvRecordsArr.push(data);
        })
        .on("end", function () {
            csvRecordsArr.shift();
  
            database.connect((error) => {
                if (error) {
                    console.error(error);
                } else {
                    let query = 'INSERT INTO employee (id, name, address, age) VALUES ?';
                    database.query(query, [csvRecordsArr], (error, res) => {
                        console.log(error || res);
                    });
                }
            });
             
            fs.unlinkSync(filePath)
        });
  
    stream.pipe(csvDataStream);
}
 
const PORT = process.env.PORT || 3000
app.listen(PORT, () => console.log(`Node app wokring on: ${PORT}

Start Node Application

Let’s invoke the node application using the suggested command.

nodemon

Here is the url that will allow you to run the app on the browser:

http://localhost:3000

Summary

In this detailed guide, we have explained the ways that will help you learn how to upload CSV file records into MySQL database in Node js application using the fast CSV, express js, and multer modules.

Exit mobile version