Node

Node Import CSV File Data to MySQL Database with HTML Form

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

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

I am Digamber, a full-stack developer and fitness aficionado. I created this site to bestow my coding experience with newbie programmers. I love to write on JavaScript, ECMAScript, React, Angular, Vue, Laravel.

Recent Posts

How to Add Active Class in Map Items in React Js

In this quick tutorial, we will show you how to quite easily add the active…

2 days ago

How to Create and Use Redux Store in React Js

In this tutorial, we will learn how to create a Redux Store in React application.…

5 days ago

How to Build Dynamic Load More Feature in React Js

This detailed guide will cover how to create the Load More button and display data…

1 week ago

How to Integrate Redux Persist to React Redux Store

In this tutorial, we will step by step learn how to configure redux persist in…

2 weeks ago

React Redux Save Data in Local Storage with Persist Tutorial

In this comprehensive tutorial, we will learn how to persist redux store's states in React…

2 weeks ago

React Js Tailwind CSS Add Light and Dark Mode Toggler Tutorial

In this guide, we are going to learn how to add dark mode and light…

2 weeks ago