Node js Upload/Import CSV to MySQL

Import and upload csv to MySQL database in Node js + Express; Through this tutorial, i am going to show you how you can import/upload CSV file to MySQL database using Node js + Express + fast-csv.

These are all very common and easy tasks. Uploading csv through form in node js + express app. And then extract the data by reading the CSV file on the node’s routes or controller. Also, inserting the extracted data into the MySQL database.

How To Import/Upload CSV File In MySQL Using Node.js

  • Step 1 – Create Node JS App
  • Step 2 – Install Required Node js Packages
  • Step 3 – Create Table in MySQL Database
  • Step 4 – Create Server.js File and Import Modules into it
  • Step 5 – Create CSV/Upload Routes
  • Step 6 – Create CSV Upload Form
  • Step 7 – Start App Server

Step 1 – Create Node JS App

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

mkdir my-app
cd my-app
npm init

Step 2 – Install Required Node js Packages

Install required node js modules; So execute the following command to install express and multer dependencies in your node js app:

npm install express multer body-parser mysql fast-csv

Step 3 – 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 4 – Create Server.js File and Import Modules into it

Create server.js file; so visit your app root directory and create it. The import above installed node js modules into it; 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')

Step 5 – Create CSV Import/Upload Routes

Create routes for image upload; so open your server.js file and add the following routes into it:

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 – Create CSV Upload Form

Create index.html file; add the following html markup code for file upload form:

<!DOCTYPE html>
<html lang="en">
    <head>
      <title>Node js upload csv file to MySQL database - Laratutorials.com</title>
      <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 - Laratutorials.com</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>

Step 7 – Start App Server

Execute the following on terminal to start app server:

//run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/

Conclusion

Import and upload csv to MySQL database in Node js + Express; Through this tutorial, you have learned how to import/upload CSV file to MySQL database using Node js + Express + fast-csv.

Recommended Node JS Tutorials

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *