I am facing an issue with converting data from an excel sheet to json format. While the other columns convert successfully, one specific column containing json data is not being converted properly and instead gets treated as a string. Using JSON.parse() on this column results in a Syntax Error. I have attempted the following solutions:
- Trying to convert the excel sheet to json using the xlsx package
- Converting the excel file to csv first and then converting it to json using the csvtojson package
Unfortunately, neither of these methods has helped me in successfully converting the problematic column into a valid json object. Below is the excerpt from 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 suggestions or assistance on resolving this issue would be greatly appreciated.