Nodejs Express RestAPI – Upload/Import CSV File to MySQL – using Fast-CSV & Multer

nodejs-upload-import-csv-file-data-to-mysql---using-fast-csv---multer---feature-image

In the tutorial, Grokonez shows how to upload & import CSV File/Data to MySQL using fast-csv and multer libs.

Related post:
Node.js Import CSV File to MySQL – using FastCSV

Technologies

  • Node.js
  • Express
  • MySQL
  • Multer
  • Fast-CSV

Goal

We create a Node.js project as below structure:

nodejs-upload-import-csv-file-data-to-mysql---using-fast-csv---multer---project-structure

CSV File ->


id,name,address,age
1,Jack Smith,Massachusetts,23
2,Adam Johnson,New York,27
3,Katherin Carter,Washington DC,26
4,Jack London,Nevada,33
5,Jason Bourne,California,36

-> Results:

nodejs-upload-import-csv-file-data-to-mysql---using-fast-csv---multer---mysql-schema

nodejs-upload-import-csv-file-data-to-mysql---using-fast-csv---multer---result

Practice

Install Express, Fast-CSV, Multer, MySQL

– Init package.json file by cmd: npm init -> Then install express, mysql, fast-csv & multer libs:


$npm install --save express, mysql, fast-csv, multer

Upload/Import MySQL Data to Excel

-> index.js file:


const fs = require('fs');
const mysql = require('mysql');
const csv = require('fast-csv');

const multer = require('multer');
const express = require('express');

const app = express();
 
global.__basedir = __dirname;
 
// -> Multer Upload Storage
const storage = multer.diskStorage({
	destination: (req, file, cb) => {
	   cb(null, __basedir + '/uploads/')
	},
	filename: (req, file, cb) => {
	   cb(null, file.fieldname + "-" + Date.now() + "-" + file.originalname)
	}
});
 
const upload = multer({storage: storage});

// -> Express Upload RestAPIs
app.post('/api/uploadfile', upload.single("uploadfile"), (req, res) =>{
	importCsvData2MySQL(__basedir + '/uploads/' + req.file.filename);
	res.json({
				'msg': 'File uploaded/import successfully!', 'file': req.file
			});
});

// -> Import CSV File to MySQL database
function importCsvData2MySQL(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();
 
            // Create a connection to the database
            const connection = mysql.createConnection({
                host: 'localhost',
                user: 'root',
                password: '12345',
                database: 'testdb'
            });
 
            // Open the MySQL connection
            connection.connect((error) => {
                if (error) {
                    console.error(error);
                } else {
                    let query = 'INSERT INTO customer (id, address, name, age) VALUES ?';
                    connection.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 a Server
let server = app.listen(8080, function () {
 
  let host = server.address().address
  let port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port)
 
})  

Sourcecode

– MySQL Script to create customer table:


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

– Sourcecode: Nodejs-Express-RestAPI-Upload-Import-CSV-to-MySQL

Leave a Reply

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