How to convert excel column with json format data to a javascript object?

Question

I have an excel sheet with a column that has data in json format. I want to convert this sheet into json. The other two columns get converted into json but this particular column cannot be treated as a json object and instead results into a string. JSON.parse() does not work on this string and throws a Syntax Error. Here are the things I have tried:

• Convert excel sheet to json using xlsx package
• Convert excel to csv to json using csvtojson package

Neither helped me successfully convert the last column into a json object Below is my code:

let xlsx = require("xlsx")
const csvtojson = require("csvtojson")
let path = require("path")
let fs = require("fs");

const inputFilePath = path.join(__dirname, './mastersheet.xlsx');
let File = xlsx.readFile(inputFilePath);
let content = xlsx.utils.sheet_to_csv(File.Sheets['Sheet6']);

fs.writeFile('./mastersheet.csv', content, (err) => {
    if (err) console.log(err)
})

csvtojson()
    .fromFile('./mastersheet.csv')
    .then((jsonObj) => {
         console.log(jsonObj);
         fs.writeFileSync("mastersheet.json", JSON.stringify(validJsonData), 'utf8', (err) => {
             if (err) console.log(err);
         })
    });

Any help is appreciated.

Answer 1

you can use convert-excel-to-json and you convert excel to json like this:

const excelToJson = require('convert-excel-to-json');
const fs = require('fs');

const result = excelToJson({
    source: fs.readFileSync('exlTest.xlsx'),
    header:{
        rows: 1
    }
});
console.log('result :', result);