How To Import CSV File In MySQL Using Node.js

WebSolutionStuff | Jul-30-2021 | Categories : MySQL Node.js

In this tutorial we will see how to import CSV file in MySQL using Node.js. Import and export CSV/EXCEL file in Node.js is most common functionalities. In Node.js we will implents import CSV file in MySQL Database. CSV stands for Comma Separated Values. A CSV file is a plain text file that stores tables and spreadsheet information. 

In this example we will use mysql driver and csvtojson package.

mysql : This is a node.js driver for mysql. It is written in JavaScript

csvtojson : module is a comprehensive nodejs csv parser to convert csv to json or column arrays.

So, let's start to implement how to import csv file in mysql using node.js

Step 1 : Create Node Application

In this step we will create node application using below commands.

mkdir import_csv_nodejs

cd import_csv_nodejs

npm init

 

 

Step 2 : Install MySQL

In this step we will install the MySQL driver using below command

npm install mysqljs/mysql

 

Step 3 : Install csvtojson module

Now install the csvtojson module using below command

npm install csvtojson

 

Step 4 : Index.js

In index.js write down the following code.

const csvtojson = require('csvtojson');
const mysql = require("mysql");

// database details  
const hostname = "localhost",
    username = "root",
    password = "",
    databsename = "import_export_demo"
  
  
// connect to the database
let con = mysql.createConnection({
    host: hostname,
    user: username,
    password: password,
    database: databsename,
});
  
con.connect((err) => {
    if (err) return console.error('error: ' + err.message);
  
    con.query("DROP TABLE products", 
        (err, drop) => {
  
        // Query to create table "products"
        var createStatament = 
        "CREATE TABLE products(Product_Name char(50), " +
        "Product_Description char(50), Original_Price int, Selling_Price int)"
  
        // Creating table "products"
        con.query(createStatament, (err, drop) => {
            if (err)
                console.log("ERROR: ", err);
        });
    });
});
  
// CSV file name
const fileName = "products.csv";
  
csvtojson().fromFile(fileName).then(source => {
  
    // Fetching the data from each row and inserting to the table "products"
    for (var i = 0; i < source.length; i++) {
        var Name = source[i]["product_name"],
            Description = source[i]["product_description"],
            O_Price = source[i]["original_price"],
            S_Price = source[i]["selling_price"]
  
        var insertStatement = "INSERT INTO products values(?, ?, ?, ?)";
        var items = [Name, Description, O_Price, S_Price];
  		
        // Inserting data of current row into database
        con.query(insertStatement, items, 
            (err, results, fields) => {
            if (err) {
                console.log("Unable to insert item at row ", i + 1);
                return console.log(err);
            }
        });
    }
    console.log("Records inserted into database successfully...!!");
});

Note : Create products.csv file in your node.js application and some dummy records in file with column name

 

 

Step 5 : Run index.js file

Now run index.js using below code and check your database

node index.js

After run this command you will get output like below screenshot.

Import CSV data into MySQL Database using Node.js


 

You may like :

Featured Post




Follow us
facebooklogo github instagram twitter