Skip to content

All topics  /  MySQL

How to Import Data From CSV File using MySQL Database?

MySQL ·

Teams applying “How to Import Data From CSV File using MySQL Database?” in a production project can use onboarding buddy to record implementation, review and debugging time, then compare that effort with tests and shipped functionality instead of treating activity as performance by itself.

Before copying the example into a live application, confirm version-specific behaviour with the official MySQL website and test it against the project’s actual database and runtime.

Hello Friends,

This tutorial shows you how to import data from CSV files using MySQL database. This tutorial will give you a simple example of importing a CSV file into a MySQL database. I want to show you that MySQL imports CSV files in a database or table.

I like importing the CSV into a new table, then updating column names and data types if needed. But it’s up to you and depends on your data. Let’s take a look at how to import a CSV in MySQL.

Step 1: Create Node Express js App


Execute the following command on the terminal to create the node js app:

mkdir my-app
cd my-app
npm init -y

Step 2: Create Table in MySQL Database

Execute the following SQL query to create a table in your database:

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

Step 3: Install express multer body-parser mysql dependencies

Execute the following command on the terminal to express multer ejs body-parser MySQL dependencies :

npm install express multer body-parser mysql fast-csv

body-parser – Node.js request body parsing middleware which parses the incoming request body before your handlers, and makes it available under req.body property. In other words, it simplifies incoming requests.

fast-CSV – Fast-CSV is library for parsing and formatting CSVs or any other delimited value file in node.

Multer – Multer is a node.js middleware for handling multipart/form-data , which is primarily used for uploading files. It is written on top of the busboy for maximum efficiency.

Mysql – A node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed.

Step 4: Create Server.js File and Route

Create server.js file; Then import express multer body-parser MySQL fast CSV dependencies in server.js and as well as create API route for import CSV file data in MySql database; as shown below:

const express = require('express')
const app = express()
const bodyparser = require('body-parser')
const fs = require('fs');
const csv = require('fast-csv');
const mysql = require('mysql')
const multer = require('multer')
const path = require('path')
//use express static folder
app.use(express.static("./public"))
// body-parser middleware use
app.use(bodyparser.json())
app.use(bodyparser.urlencoded({
    extended: true
}))
// Database connection
const db = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "test"
})
db.connect(function (err) {
    if (err) {
        return console.error('error: ' + err.message);
    }
    console.log('Connected to the MySQL server.');
})
//! Use of Multer
var storage = multer.diskStorage({
    destination: (req, file, callBack) => {
        callBack(null, './uploads/')    
    },
    filename: (req, file, callBack) => {
        callBack(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname))
    }
})
var upload = multer({
    storage: storage
});
//! Routes start
//route for Home page
app.get('/', (req, res) => {
    res.sendFile(__dirname + '/index.html');
});
//@type   POST
//route for post data
// -> Express Upload RestAPIs
app.post('/api/uploadfile', upload.single("uploadfile"), (req, res) =>{
    UploadCsvDataToMySQL(__dirname + '/uploads/' + req.file.filename);
    res.json({
        'msg': 'File uploaded/import successfully!', 'file': req.file
    });
});
function UploadCsvDataToMySQL(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();
        // Open the MySQL connection
        db.connect((error) => {
            if (error) {
                console.error(error);
            } else {
                let query = 'INSERT INTO customer (id, address, name, age) VALUES ?';
                db.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 connection
const PORT = process.env.PORT || 3000
app.listen(PORT, () => console.log(`Server is running at port ${PORT}`))

Step 5: Start App Server

You can use the following command to start node js upload CSV file to MySQL database app server:

//run the below command
npm start

Step 6: Call Rest API with CSV File

Open your postman app and call country state city list APIs; as shown below:

Countries List Api

URL :- http://localhost:3000/api/uploadfile
Method :- POST

Important note :- Call the above api with Body “application/x-www-form-urlencoded”.

More on this topic