Import data from excel to collection

@Or

I get this error

Error: res.Buffer is not a function

import {XLSX} from 'exceljs';

export async function uploadButton1_change(event) {

    // console.log(event);
    const file = await $w('#uploadButton1').startUpload();
    // console.log("https://ae269d04-1741-4200-bc92-2e27c7d549cd.usrfiles.com/ugd/"+file.url.substring(18, 62));
    let url = "https://ae269d04-1741-4200-bc92-2e27c7d549cd.usrfiles.com/ugd/"+file.url.substring(18, 62)
    const buffer = await fetch(url).then(res => res.Buffer());
//    console.log(buffer);
    var workbook = new XLSX.Workbook();
    
    await workbook.xlsx.load(buffer);
    var worksheet = workbook.getWorksheet(1);
    let rows = [];
    let values = [];
    worksheet.eachRow(function (row, rowNumber) {
        if (rowNumber === 1) values = row.values.slice(1);
        else {
            rows[rowNumber - 2] = row.values.slice(1);
        }
    });
    let json = rows.map(function (x) {
        let ret = {};
        values.forEach((val, index) => {
            ret[val] = x[index];
        })
        return ret
    })

    console.log(json); 
}

And if I fix the code like this:

    const buffer = await fetch(url).then(res => res.arrayBuffer());

I get this error

Error: Cannot read properties of undefined (reading 'Workbook')

Hi,

I believe you can't just send the file url to the function, it has to be saved in the file system first, you can't access the file system directly with Corvid so you should try and use the Read() function from "exceljs" which gets a stream instead of the readFile() function.

Good luck :)

Or

Hi Or,

thanks for your answer.

I was trying to use exceljs but couldn't make it work.

My backend code:

import exceljs from 'exceljs';
const ExcelJS = require('exceljs');


export async function readExcel(file) {
 var workbook = new ExcelJS.Workbook();
 const newfile = await workbook.xlsx.readFile(file);
 return newfile;
}

My front-end code:

import {readExcel} from 'backend/be';

let file = "https://bc30d748-475c-4bb5-9110-8d9927e62a4a.usrfiles.com/ugd/bc30d7_43886722bb4449acb77d89e84889a789.xlsx";  
  readExcel(file).then(product => {
      console.log(product);
  })
  .catch(error => {
    console.log(error);
  });

and I have this error:

Error: File not found: https://bc30d748-475c-4bb5-9110-8d9927e62a4a.usrfiles.com/ugd/bc30d7_43886722bb4449acb77d89e84889a789.xlsx

Any ideas?

Hi,

You can try and use one of the excel node modules(right now we support "excel" and "exceljs") to read the file, then you can use bulkInsert() to insert the data to a collection.

Let me know if you need further help,

Or