Parse XLS file with multiple sheets

Queries and Resources
1

My company uses a software that generates a file called cutlist.xls with 10 different sheets, all identical names every time. This file is generated on a per job basis, sometimes between 10 and 200 a day. Currently, we have been doing this task on excel using VBA however I would prefer to implement a better system than using excel as a database.

When I use the file picker to upload the XLS file, the sheet names are loaded as column headers, and the contents of each sheet is shown in text format similar to a CSV.

How would I be able to go about displaying specific pages in a column format? I do not require every sheet, there are 3 I require every single time. I need these 3 sheets displayed into 3 different tables, Table1, Table2 and Table3.

Any help would be greatly appreciated.

Thanks,
Chris

2

@weilerc

Hey there :wave: Would you mind sharing some screenshots of what your table currently looks like and pointing out what you would like it to look like?

3

I wanted to share my solution for parsing an Excel file using the File Input component and Retool's File Utilities. This approach has been tested and works successfully.

To start, add a File Input component (in this example, named filepicker1) and a Button to import the data.

In the "onClick" action of the Import Button, use the following code to parse the Excel file:

javascriptCopy code

const file = filepicker1.files[0];
const results = await fileUtils.parseXLSX(filepicker1.value[0]);
console.log(results);

This code accesses the selected file using filepicker1.files[0] and parses the data using fileUtils.parseXLSX(). The parsed data is then logged in the console.

if you have multiple sheets and you want to fetch the data from each sheet separately then you can use the following command
for example, you have the 3 sheets in the xlsx file
sheet_1 , sheet_2 , sheet_3

const sheet1_data = results.sheet1
 console.log("all results", results);

for (let i = 0; i < sheet1_data.length; i++) {
  console.log(sheet1_data[i]);
}

For more information on Retool's File Utilities, you can refer to the official documentation.

I hope this solution helps others who are looking to parse Excel files in their Retool apps.