Node js Import CSV File Data to MySQL Database Tutorial

Node js Import CSV File Data to MySQL Database Tutorial

In this tutorial, you will learn how to import CSV files in Node js express using multer & fast CSV and store or insert data in MySQL database.

How to Import CSV File In MySQL Using Node.js Express

Steps to import CSV file in node js express using multer & fast csv and insert data in MySQL DB:

Step 1 – Create Node Express js App

Run the following command on the terminal to create a node js app:

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

Step 2 – Create a Table in MySQL Database

Execute the following SQL query to create a table in 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

Run 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` field that allows users to choose the csv file for upload:

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

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 – Test Application

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/

Conclusion

Upload CSV file in mysql database using node js; In this tutorial, you have learned how to upload CSV file data into MySQL database using Node js + express + multer + fast CSV.

Recommended Node JS Tutorials

AuthorAdmin

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including PHP, Python, JavaScript, jQuery, Laravel, Livewire, CodeIgniter, Node.js, Express.js, Vue.js, Angular.js, React.js, MySQL, MongoDB, REST APIs, Windows, XAMPP, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL, and Bootstrap. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

Leave a Reply

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