How to import data from .CSV file into MySQL table using Node.js ?

Last Updated : 19 Jan, 2022

What is .CSV file? The .CSV (Comma Separated Values) files are plain text files that contains a list of data separated by comma(,). It is a format best used for tabular data, row, and columns, exactly like a spreadsheet, just the difference is that the file is in the form of plain text. The idea of a CSV file is to perform export and import complex data from one application to another. Note: Here, we are using sample.csv to demonstrate our program and procedure. In sample.csv we have stored the user's Name, Age, Email, and City.

Import CSV into MySQL using Node.js:

Create a node.js project using "npm init" and save your .csv file in the same directory.
Install two packages "mysql" and "csvtojson" using the following command:
```
npm i mysql csvtojson
```
mysql driver: This is a node.js driver for mysql. It is written in JavaScript, that does not require compiling. We are using it to establish connection with our MYSQL database and perform queries.
csvtojson: Its a csv parser to convert csv to json or column arrays.
Now just create a file like index.js and write down the following code:

javascript

// Importing mysql and csvtojson packages
// Requiring module
const csvtojson = require('csvtojson');
const mysql = require("mysql");

// Database credentials
const hostname = "localhost",
    username = "root",
    password = "root",
    databsename = "csvtomysql"


// Establish connection 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 sample", 
        (err, drop) => {

        // Query to create table "sample"
        var createStatament = 
        "CREATE TABLE sample(Name char(50), " +
        "Email char(50), Age int, city char(30))"

        // Creating table "sample"
        con.query(createStatament, (err, drop) => {
            if (err)
                console.log("ERROR: ", err);
        });
    });
});

// CSV file name
const fileName = "sample.csv";

csvtojson().fromFile(fileName).then(source => {

    // Fetching the data from each row 
    // and inserting to the table "sample"
    for (var i = 0; i < source.length; i++) {
        var Name = source[i]["Name"],
            Email = source[i]["Email"],
            Age = source[i]["Age"],
            City = source[i]["City"]

        var insertStatement = 
        `INSERT INTO sample values(?, ?, ?, ?)`;
        var items = [Name, Email, Age, City];

        // 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(
"All items stored into database successfully");
});

Run index.js file using the following command:

node index.js

Output:

Explanation of code:

At the first two lines of code we Import mysql and csvtojson.

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