Read Excel File in JSON Format Using Nodejs

This tutorial help to read excel file using nodejs. We’ll let you know to read the excel file in JSON format and insert it into MySQL data or NoSQL database using rest API.

The read-excel-file module help to read small to medium *.xlsx files in a browser or Node.js. It parses file data to an array of JSON objects using a schema.

I am also using chalk module for coloring in console window.

The Axios HTTP client is a Promise-based HTTP client for the react/nodejs application.

Checkout Other Nodejs tutorials:

• Local Storage with Angularjs
• How To Handle Routing In React Using BrowserRouter
• How To Use Localstorage in Node js
• React File Upload In 5 Minute
• Autocomplete Example Using React

Read Excel File and Insert Data Using Nodejs

We’ll use Read Excel File npm module to read the data from excel file in a json format.

Let’s create index.js file into the project folder. I have added below line at the top of the file:

#!/usr/bin/env node
const chalk = require("chalk");
const readXlsxFile = require('read-excel-file/node')
const axios = require('axios');

As We know:

– The first line that begins with #! is usually called a “shebang.” This is normally only used on Linux or UNIX operating systems to inform the system what type of script is included in the rest of the text file.
– chalk: This is used for terminal string styling.
– read-excel-file: Help to read excel file.
– axios: HTTP Client to save data into the db using API.

Define Schema

Let’s Define schema for excel sheet.

const schema = {
    'Emp Name': {prop: 'emp_name',type: String},
    'Salary': { prop: 'salary', type: Integer},
    'Age': {prop: 'age', type: Integer}
}

Read Data From Excel Sheet

Let’s read excel file using npm module.

try {
    readXlsxFile('data.xlsx', { schema }).then(({ rows, errors }) =>  {
        rows.forEach((row, k) => {
            //validate data
            let isValid = isValidateData(row);

            if(isValid) {
                console.log(chalk.green.bold('Data is valid for row:'+ k+1));
                createData(row);
            } else {
                console.log(chalk.red.bold('Data is not valid for row:'+ k));
            }
            //console.log(row);
          });
    })

} catch (error) {
    console.error(error)
    throw new Error('invalid file.')
}

console.log('Press any key to exit');

process.stdin.setRawMode(true);
process.stdin.resume();
process.stdin.on('data', process.exit.bind(process, 0));

We have used readXlsxFile instance and passed the excel source file path and schema, if everything is fine then we’ll get all excel data in a row as a JSON format.

Nodejs Method to Save Data into the Database

We have a JSON object, I am going to save the JSON object into the MySQL database.

axios.post('https://dummy.restapiexample.com/api/v1/employees', row)
.then(res => console.log(res.data));

We have used POST type method and passed employee row data as a payload.