Node Import CSV File Data to MySQL Database with HTML Form

Last updated on: September 17, 2023 by Digamber

In this tutorial, you will discover how to import CSV file into MySQL database using Node js and Fast CSV package.

Uploading files to databases is made easy by the Multer package. Multer is a popular npm package that enables file uploading less excruciating.

It is made for node js, a powerful middleware that handles multipart/form-data and is predominantly used for uploading files.
We will combine multer with Fast-csv module for importing files to the MySQL database.

Fast CSV is a handy package that parses and formats CSVs or any other delimited value file in the node js environment.

There are also other modules we will use in this guide; those packages will help us create the csv file import feature in Node js.

How to Data from CSV File into MySQL Database using Node Js

Step 1: Create Node App
Step 2: Install NPM Modules
Step 3: Generate MySQL Table
Step 4: Create HTML File Import Form
Step 5: Create Server File
Step 6: Run Node Project

Create Node App

Get onto the command prompt of your terminal and run the given command to make the empty folder for node project.

mkdir node-csv

Enter into the empty folder:

cd node-csv

Type the suggested command, then run the given command to generate the new package.json file.

npm init

Now, the node script has been created, it needs to be registered into the scripts section as given, make sure to add the script name in package.json file.

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

Install NPM Modules

Furthermore, we need to type the given command on the terminal and hit enter to install the given modules.

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

Generate MySQL Table

It is easy to create a new table for storing CSV file records, you can create the MySQL data table using the given SQL command.

CREATE TABLE `users` (
  `id` bigint(11) NOT NULL,
  `name` varchar(150) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE= InnoDB DEFAULT CHARSET=utf8

Create HTML File Import Form

Let us make an index.html file, this file will handle the view of the feature.

We have to add the Bootstrap 5 link in the head section, this will allow us to create file upload UI component.

<!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>

Create Server File

Open the app.js file, in this file you have to place the given code line by line.

In this file, we are making the database connection to MySQL, creating the routes that will handle the csv file upload to database and setting up the node app 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 db = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "test"
})
 
db.connect(function (err) {
    if (err) {
        return console.error(err.message);
    }
    console.log('Connected to database.');
})
 
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('/import-csv', upload.single("import-csv"), (req, res) =>{
    uploadCsv(__dirname + '/uploads/' + req.file.filename);
    console.log('File has imported :' + err);
});
 
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();
  
            db.connect((error) => {
                if (error) {
                    console.error(error);
                } else {
                    let query = 'INSERT INTO users (id, name, email) VALUES ?';
                    db.query(query, [csvDataColl], (error, res) => {
                        console.log(error || res);
                    });
                }
            });
             
            fs.unlinkSync(uriFile)
        });
  
    stream.pipe(fileStream);
}
 
const PORT = process.env.PORT || 5555
app.listen(PORT, () => console.log(`Node app serving on port: ${PORT}

Run Node Project

Here is the command that will start the node app, the nodemon module will restart your node server even if you make the minor changes in the node app’s files.

nodemon

You can type the given URL on the browser to test the app.

http://localhost:5555

Node Import CSV File Data to MySQL Database with HTML Form

Conclusion

Node js is a great framework, and it allows developers to build any feature they want for their web applications.

Thanks to the npm community, which provides almost every type of module that is needed to build desired functionalities.

In this tutorial, we learned how to upload CSV file to MySQL database using Fast CSV, Multer, MySQL, and most importantly, express js.

Digamber

A Full-stack developer with a passion to solve real world problems through functional programming.