Converting an Excel file to CSV and reading its contents in JSON format

Question

When an excel file is uploaded in my program, it needs to get converted to a CSV file to be read. The process is working fine and I am using asyc/await, however whenever I try to read the file using the csvtojson package in Node, the file does not get read properly. If I directly use a CSV file then it works fine. The issue arises when the conversion occurs.

product.js

const multer = require('multer');
const express = require('express');
const router = express.Router();
const csv = require('csvtojson');
const fs = require('fs');
const xlsx = require('node-xlsx');

router.post('/upload', upload.single('singleFile'), async (req, res) => {
    let csvFilePath = req.file.path;
    let fileType = req.file.mimetype;

    const convertToCSV = async _ => {
        console.log("2");
        if (fileType === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ||
            fileType === 'application/vnd.ms-excel') {
            let obj = xlsx.parse(csvFilePath);
            let rows = [];
            let writeStr = "";

            for (let i = 0; i < obj.length; i++) {
                let sheet = obj[i];
                for (let j = 0; j < sheet['data'].length; j++) {
                    rows.push(sheet['data'][j]);
                }
            }

            //creates the csv string to write it to a file
            for (let i = 0; i < rows.length; i++) {
                writeStr += rows[i].join(",") + "\n";
            }

            console.log("3");
            fs.writeFile("csv/out.csv", writeStr, function (err) {
                if (err) {
                    return res.status(400).send({'error': err});
                }
                console.log("4");
                console.log("out.csv was saved in the current directory!");
            });
        }
    }
    console.log("1");
    await convertToCSV().then(async _ => {
        console.log("5");
        const jsonArray = await csv({flatKeys: true})
            .fromFile(csvFilePath)
            .then(async (jsonObj) => {
                console.log("6");
                console.log(jsonObj[0]);
                ...
                
                //Few more functions
                
            }).catch(err => {
                return res.status(400).send(err);
            });
    });
});

My console log looks like this

1
2
3
5
4
out.csv was saved in the current directory!
6
{
  'PK\u0003\u0004\u0014\u0000\u0006\u0000\b\u0000\u0000\u0000!\u0000b�h^\u0001\u0000\u0000�\u0004\u0000\u0000\u0013\u0000\b\u0002[Content_Types].xml �\u0004\u0002(�\u0000\u0002\u0000\u0000\
u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u00

Whereas if a new CSV file is uploaded or an existing CSV is used then the output for the console.log(jsonObj[0]); is

{
  'Column1': 'Column 1 Data',
  field2: 'field2 Data',
  field3: 'field 3 Data',
  Categories: 'categories',
  ....
}

I added await before fs.writeFile however the same issue arises. There are two files that get saved under the directory csv/

c33129f3bdef482657992dbf452d2c1b
out.csv

And the contents of the previous file are read (assuming that, since they are very similar) and out.csv is not read.

Update

Wrapped a promise around fs.writeFile and the console is ordered now, however the output for the data read is yet the same:

 const convertToCSV = async _ => {
        return new Promise(((resolve, reject) => {
            console.log("2");
            ....

                console.log("3");
                fs.writeFile("csv/out.csv", writeStr, function (err) {
                    if (err) {
                        return res.status(400).send({'error': err});
                    }
                    console.log("4");
                    console.log("out.csv was saved in the current directory!");
                    resolve();
                });
            }
        }));
    }

Console Log

1
2
3
4
out.csv was saved in the current directory!
5
6
{
  'PK\u0003\u0004\u0014\u0000\u0006\u0000\b\u0000\u0000\u0000!\u0000b�h^\u0001\u0000\u0000�\u0004\u0000\u0000\u0013\u0000\b\u0002[Content_Types].xml �\u0004\u0002(�\u0000\u0002\u0000\u0000\
u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u00

Anatoly · Accepted Answer · 2020-11-01T20:05:10.627

You don't wait fs.writeFile("csv/out.csv" that's why you get 5 before 4 in console. You should wrap your function content into Promise:

const convertToCSV = async _ => {
  return new Promise((resolve, reject) => {
     console.log("2");
     ...
     console.log("3");
     fs.writeFile("csv/out.csv", writeStr, function (err) {
        if (err) {
          // I'd prefer to call "reject" here and add try/catch outside for sending 400
          return resolve(res.status(400).send({'error': err}));
        }
        console.log("4");
        console.log("out.csv was saved in the current directory!");
        resolve();
     });
  })
)

Also you read csvFilePath that contains a filename of Excel file and not a CSV that was stored under csv/out.csv name.

Converting an Excel file to CSV and reading its contents in JSON format

1 Answers1