How to extract data from xlsx or csv file as JSON?-CodePudding

I was tring to make an app in which a user can upload an xlsx or csv file from frontend and submit the file to backend made with nodejs and express then process the data in the backend itself. But while recieving data in the backend, I am getting it in the below format

 input1: {
    name: 'report.xlsx',
    data: <Buffer 50 4b 03 04 14 00 08 08 08 00 c3 63 10 4b 00 00 00 00 00 00 00 00 00 00 00 00 0b 00 00 00 5f 72 65 6c 73 2f 2e 72 65 6c 73 ad 92 cf 4a 03 31 10 87 ef ... 5375 more bytes>,
    size: 5425,
    encoding: '7bit',
    tempFilePath: '',
    truncated: false,
    mimetype: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    md5: '823b3f7a9b47992e039ff459b121bc12',
    mv: [Function: mv]
}

How do I extract the data from the buffer received in the backend as json? I need to access the data as a json object in the express backend.

Here is my express backend file (index.ts):

app.post('/partners', async function (req, res) {
   if (!req.files || Object.keys(req.files).length === 0) {
     return res.status(400).send('No files were uploaded.')
   }
   let myFile = req.files
   console.log(myFile)
 }
})

And here is the code for frontend:

<!DOCTYPE html>
<html lang="en">
 <head>
  <meta charset="UTF-8" />
  <meta
   http-equiv="X-UA-Compatible"
   content="IE=edge" />
  <meta
   name="viewport"
   content="width=device-width, initial-scale=1.0" />
  <link
   rel="stylesheet"
   href="./styles/styles.css" />

  <title>Upload </title>
 </head>

 <form
  ref="uploadForm"
  id="uploadForm"
  action="/partners"
  method="post"
  encType="multipart/form-data">
   <input
     id="upload" type=file  name="input1"
     placeholder="Upload file"  />
   <button type="submit">Submit</button>
  </form>
 </body>
</html>

CodePudding user response：

I solved this using xlsx and to-arraybuffer npm packages

Run:

npm i xlsx to-arraybuffer

Then for the post route,

app.post('/my-route', async function (req, res) {
  let sampleFile

  if (!req.files || Object.keys(req.files).length === 0) {
    return res.status(400).send('No files were uploaded.')
  }

  //we convert array buffer to json here....
  const arrayBufferToJson = (buff) => {
    let arrBuff = toArrayBuffer(buff)
    const report = XLSX.readFile(arrBuff, { raw: false })
    const worksheet = report.Sheets[report.SheetNames[0]]
    const data = XLSX.utils.sheet_to_json(worksheet)
    console.log(data)
    return data
  }
  sampleFile = req.files
  if (sampleFile.sampleFile.length > 1) {
    //multiple files
    let files = sampleFile.sampleFile
    files.forEach(async (file) => {
      //returns data as json array
      arrayBufferToJson(file.data)
    })
  } else {
    //single file
    sampleFile = req.files.sampleFile
    //returns data as json array
    arrayBufferToJson(sampleFile.data)
  }
})