Import data from excel to collection

Ask the community
,
1

Hi,
I want to import data from excel file that the user is uploading to a collection.
Any ideas on how to do that?

2

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

3

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?

4

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 :slight_smile:
Or

5

Hi,
when I am trying to use the Read function instead of readFile I get this error:
TypeError: stream.pipe is not a function

should I import another library?

6

@zivassor I ran into that problem as well, I believe the Load function is working,
How do you get the excel file’s URL from the user?

7

@_or Unfortunately the Load function isn’t working and I get this error:
Error: Can’t find end of central directory : is this a zip file ? If it is, see How to read a file — wixCodeNamespacesAndElementorySupport.min.js:0

The user should upload the file. For now, I just typed a url.

8

Hi,
I managed to make it work with the Load function :slight_smile:
Check out this code and try it with your URL

export function getExcelContent(url) {
 return fetch(url).then(function (res) {
 /* get the data as a Blob */
 if (!res.ok) throw new Error("fetch failed");
 return res.buffer()
        })
        .then(async function (ab) {
 /* data received as buffer */
 var workbook = new XLSX.Workbook();
 await workbook.xlsx.load(ab);
 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
            })

 return (json)
        });
}

Good luck,
Or

9

@_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’)

10

Hey! This is a great solution and I have been using it with great success for the past few months. But since yesterday, I’m encoutering the following error when uploading excel files and reading them using exceljs.

Error loading web module backend/receivingExcelProcessor.jsw: Cannot find module ‘node:events’

This is super frustrating as I have no idea why this is happening. I’ve noticed that removing the exceljs import allows the rest of the module to run, but all excel functionality is lost.
Any insight into why this is happening is greatly appreciated.