Upload or Import CSV File Data to MySQL Database in Node.js

Uploading or importing CSV file data is the process of transferring data from a CSV file to another system, such as a database or spreadsheet. CSV (comma-separated values) files are a common format for storing data in a tabular format, with each row representing a single record and each column representing a different field.

This tutorial will teach you how to upload or import CSV file data to a MySQL database in Node.js.This tutorial will create a simple CSV file upload form; This form will send the CSV file data to the node js route. In the router method; reading this CSV file, the data will be inserted/uploaded to the MySQL database.

Table of Contents

Step 1: Create Node Express js App
Step 2: Create Table in MySQL Database
Step 3: Install express body-parser mysql dependencies
Step 4: Create Html File Upload/Import Form
Step 5: Create Server.js File
Step 6: Start App Server

Step 1: Create Node Express js App

Execute the following command on terminal to create node js app:

mkdir my-app
cd my-app
npm init -y

Step 2: Create Table in MySQL Database

Execute the following sql query to create a table into your database:

CREATE TABLE `customer` (
  `id` bigint(20) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Step 3: Install express multer body-parser mysql dependencies

Execute the following command on the terminal to express multer ejs body-parser mysql dependencies :

npm install express multer body-parser mysql fast-csv

Step 4: Create Html File Upload/Import Form

Create a form with a `file input` element that allows us to choose the csv file and a button to submit the form; So create index.html file and add the following code into it:

<!DOCTYPE html>
<html lang="en">
    <head>
      <meta charset="UTF-8">
      <meta name="viewport" content="width=device-width, initial-scale=1">
    </head>
    <body>
      <h1>Node js upload csv file to MySQL database</h1>
      <form action="/uploadfile" enctype="multipart/form-data" method="post">
        <input type="file" name="uploadfile" accept='csv' >
        <input type="submit" value="Upload CSV">
      </form>  
    </body>
</html>

Make sure your form must have enctype="multipart/form-data"attribute and form method should be post.

Step 5: Create Server.js File

Create server.js file and import express multer body-parser mysql dependencies in server.js; as shown below:

const express = require('express')
const app = express()
const bodyparser = require('body-parser')
const fs = require('fs');
const csv = require('fast-csv');
const mysql = require('mysql')
const multer = require('multer')
const path = require('path')
 
 
//use express static folder
app.use(express.static("./public"))
 
// body-parser middleware use
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('error: ' + err.message);
    }
    console.log('Connected to the MySQL server.');
})
 
//! Use of 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
});
 
//! Routes start
 
//route for Home page
app.get('/', (req, res) => {
  res.sendFile(__dirname + '/index.html');
});
 
//@type   POST
// upload csv to database
app.post('/uploadfile', upload.single("uploadfile"), (req, res) =>{
    UploadCsvDataToMySQL(__dirname + '/uploads/' + req.file.filename);
    console.log('CSV file data has been uploaded in mysql database ' + err);
});
 
function UploadCsvDataToMySQL(filePath){
    let stream = fs.createReadStream(filePath);
    let csvData = [];
    let csvStream = csv
        .parse()
        .on("data", function (data) {
            csvData.push(data);
        })
        .on("end", function () {
            // Remove Header ROW
            csvData.shift();
  
            // Open the MySQL connection
            db.connect((error) => {
                if (error) {
                    console.error(error);
                } else {
                    let query = 'INSERT INTO customer (id, address, name, age) VALUES ?';
                    db.query(query, [csvData], (error, response) => {
                        console.log(error || response);
                    });
                }
            });
             
            // delete file after saving to MySQL database
            // -> you can comment the statement to see the uploaded CSV file.
            fs.unlinkSync(filePath)
        });
  
    stream.pipe(csvStream);
}
 
//create connection
const PORT = process.env.PORT || 3000
app.listen(PORT, () => console.log(`Server is running at port ${PORT}`))

Step 6: Start App Server

You can use the following command to start node js upload csv file to mysql database app server:

//run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/

In this tutorial, you have learned how to upload CSV file data into MySQL database using Node js, Express, Multer and fast CSV.

#nodejs #mysql