Node CSV file import to MySQL database example; In this detailed guide, we will show you how to import CSV file data to MySQL database in Node js application.

To upload the CSV file records in MySQL via Node js requires us to create REST API using Express js. Our express api routes will allow your node application to read the CSV data and then insert or add the CSV records into the MySQL database.

We will build the REST API route for sending or uploading CSV data to MySQL using the Express, Multer, and Fast CSV packages. Let us go through the provided instruction to import CSV data into MySQL using Node js.

How to Create Import CSV File Data in MySQL Database REST API with Node Js

• Step 1: Build Node Project
• Step 2: Install Required Packages
• Step 3: Add Table in MySQL
• Step 4: Prepare Node Server
• Step 5: Run Node App

Build Node Project

Generate the new blank folder for keeping the application files using the provided command.

mkdir node-proxima

Get into the application directory:

cd node-proxima

Go to terminal, type the given command on command prompt for creating the package.json file.

npm init

Make the app.js file in project folder, specifically at the root. Make sure to register this file’s name into the package.json file.

{
  "main": "app.js",
}

Install Required Packages

Type the provided commands on the terminal’s command prompt, execute the following command to add express fast-csv multer mysql body-parser nodemon packages in node app.

npm install express fast-csv multer mysql body-parser nodemon

Add Table in MySQL

You need a brand new table for storing the CSV file data, therefore you have to invoke the provided command to create a new employee table.

CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `age` int(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE= InnoDB DEFAULT CHARSET=utf8

Prepare Node Server

We now have to configure the node server, make sure to insert the given code into the app.js file.

const express = require('express')

const bodyparser = require('body-parser')

const mysql = require('mysql')

const fs = require('fs')

const csv = require('fast-csv')

const path = require('path')

const multer = require('multer')

const app = express()

app.use(express.static('./public'))

app.use(bodyparser.json())
app.use(
  bodyparser.urlencoded({
    extended: true,
  }),
)

const database = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'demo',
})

database.connect(function (err) {
  if (err) {
    return console.error('error: ' + err.message)
  }
  console.log('App connected to MySQL.')
})

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,
})

app.get('/', (req, res) => {
  res.sendFile(__dirname + '/index.html')
})

app.post('/api/importcsvdata', upload.single('importcsvdata'), (req, res) => {
  nodeToMySQL(__dirname + '/uploads/' + req.file.filename)
  res.json({
    msg: 'File successfully inserted!',
    file: req.file,
  })
})

function nodeToMySQL(csvUrl) {
  let stream = fs.createReadStream(csvUrl)
  let csvArray = []
  let csvDataStream = csv
    .parse()
    .on('data', function (data) {
      csvArray.push(data)
    })
    .on('end', function () {
      csvArray.shift()

      database.connect((error) => {
        if (error) {
          console.error(error)
        } else {
          let query = 'INSERT INTO employee (id, name, address, age) VALUES ?'
          database.query(query, [csvArray], (error, res) => {
            console.log(error || res)
          })
        }
      })

      fs.unlinkSync(csvUrl)
    })
  stream.pipe(csvDataStream)
}

const PORT = process.env.PORT || 3000
app.listen(PORT, () => console.log(`Node app wokring on: ${PORT}

Run Node App

We have almost created the REST api for uploading the csv file into the MySQL database, eventually run the node app using given command.

nodemon

You can use the given API, ensure that you set the method to POST and test the api with Postman:

http://localhost:3000/api/importcsvdata 

Summary

In this exhaustive tutorial, we have learned in the simplest ways how to import CSV file data into MySQL database using Node js, fast CSV, express js, and multer modules from absolute scratch.