Node.js Import CSV File to MySQL – using FastCSV

nodejs-import-csv-file-to-mysql---using-fast-csv---feature-image

In the tutorial, Grokonez shows how to import data from CSV File to MySQL with Node.js using fast-csv lib.

Technologies

  • Node.js
  • MySQL
  • Fast-CSV lib

Goal

We create a Node.js project as below structure:

CSV File ->

-> Results:

Practice

Init package.json file

Init package.json file:


D:\gkz\article\Nodejs-Import-CSV-File-to-MySQL>npm init
This utility will walk you through creating a package.json file.
It only covers the most common items, and tries to guess sensible defaults.

See `npm help json` for definitive documentation on these fields
and exactly what they do.

Use `npm install ` afterwards to install a package and
save it as a dependency in the package.json file.

Press ^C at any time to quit.
package name: (nodejs-import-csv-file-to-mysql)
version: (1.0.0)
description: Node.js Import CSV File to MySQL - using CSV Fast-CSV
entry point: (index.js) index.js
test command:
git repository:
keywords: Nodejs, CSV File, Fast-CSV
author: ozenero.com
license: (ISC)
About to write to D:\gkz\article\Nodejs-Import-CSV-File-to-MySQL\package.json:

{
  "name": "nodejs-import-csv-file-to-mysql",
  "version": "1.0.0",
  "description": "Node.js Import CSV File to MySQL - using CSV Fast-CSV",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "Nodejs",
    "CSV",
    "File",
    "Fast-CSV"
  ],
  "author": "ozenero.com",
  "license": "ISC"
}


Is this ok? (yes) yes

Install MySQL & Fast-CSV lib

$ npm install mysql
$ npm install fast-csv

-> package.json:


{
   "name": "nodejs-import-csv-file-to-mysql",
   "version": "1.0.0",
   "description": "Node.js Import CSV File to MySQL - using CSV Fast-CSV",
   "main": "index.js",
   "scripts": {
     "test": "echo \"Error: no test specified\" && exit 1"
   },
   "keywords": [
     "Nodejs",
     "CSV",
     "File",
     "Fast-CSV"
   ],
   "author": "ozenero.com",
   "license": "ISC",
   "dependencies": {
     "fast-csv": "^2.4.1",
     "mysql": "^2.16.0"
   }
 }

Import CSV File to MySQL

-> index.js file:
const fs = require('fs');
const mysql = require('mysql');
const csv = require('fast-csv');

// Import CSV Data to MySQL database
importCsvData2MySQL('customers.csv');

function importCsvData2MySQL(filename){
    let stream = fs.createReadStream(filename);
    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);
                    });
                }
            });
        });

    stream.pipe(csvStream);
}

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-Import-CSV-to-MySQL

2 thoughts on “Node.js Import CSV File to MySQL – using FastCSV”

  1. I’m excited to find this web site. I wanted to thank you for ones time due to this fantastic read!!
    I definitely appreciated every bit of it and I have you book marked to see new things in your blog.

  2. Hi there just wanted to give you a brief heads up and let you
    know a few of the images aren’t loading correctly. I’m not sure why but I think its a linking issue.
    I’ve tried it in two different browsers and both show the same outcome.

Leave a Reply

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