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 :

Recommended Post
Featured Post
Laravel 11 Drag and Drop File Upload with Dropzone JS
Laravel 11 Drag and Drop File...

Hello web developers! In this article, we'll see laravel 11 drag and drop file upload with dropzone js. He...

Read More

May-06-2024

7+ Laravel tips: DB Models and Eloquent (2023)
7+ Laravel tips: DB Models and...

Welcome to our latest guide on mastering Laravel's DB Models and Eloquent, updated for 2023. If you're a web dev...

Read More

Nov-08-2023

How To Validate International Phone Number Using jQuery
How To Validate International...

In this article, we will see how to validate international phone numbers using jquery. Here, we will learn about mo...

Read More

May-12-2023

How to Validate Empty Input Field in jQuery
How to Validate Empty Input Fi...

In the dynamic world of web development, form validation is a crucial aspect of creating a user-friendly and error-free...

Read More

Sep-25-2023