Manipulate xlsx file and send it to s3 bucket

App Building
1

Hi everyone,
I'm trying these days to build retool app that takes file input from the user and manipulate it and then send it to a s3 bucket.

I tried to do this by using a transformer that accepts the value of the file from the file input component, and return the modified file.
I used the gpt to write the code but it no seems to be work.

const file = {{ fileInput1.value["0"]}};

// Read the XLSX file using FileReader
const reader = new FileReader();
reader.onload = () => {
const fileData = reader.result;

// Create a new workbook instance
const workbook = XLSX.read(new Uint8Array(fileData), { type: 'array' });

// Get the first sheet
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Delete the first row (assuming it's the header row)
const rows = Object.keys(worksheet).filter(cell => /^[A-Z]+1$/.test(cell));
rows.forEach(row => delete worksheet[row]);

// Rename the first column header to "name"
worksheet['A1'].v = 'name';

// Rename the second column header to "id"
worksheet['B1'].v = 'id';

// Convert the modified workbook to binary
const modifiedData = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });

// Create a new Blob with the modified XLSX data
const modifiedFile = new File([new Uint8Array(modifiedData)], 'modified.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });

// Assign the modified XLSX file to the output property
modifiedFile;
};

// Read the XLSX file as array buffer
reader.readAsArrayBuffer(file);

this is the code I've tried to run, but it caused an error.
the value of this {{ fileInput1.value["0"]}} expression is a very very long string.
I will appreciate any help or try of help.

best regards,
Tomer.

2

Hey @Tomer_Gabay!

To use the XLSX library you'll first need to import it as a preloaded library, you can try with one of the links here.

Once that's done, you can actually parse your data with

const workbook = XLSX.read(fileInput1.value[0], { type: 'base64' });

The data will be stored as a base64 string in the Retool model so there's no need to use the FileReader API as long as you specify the correct type. Similarly, you'll likely want to write to a base64 string as well as that can pass between queries nicely, e.g.

return XLSX.write(workbook, { bookType: 'xlsx', type: 'base64' });

All together, that might look something like:

const workbook = XLSX.read(fileInput.value[0], {type: "base64"});

// Get the first sheet
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Delete the first row (assuming it's the header row)
const rows = Object.keys(worksheet).filter(cell => /^[A-Z]+1$/.test(cell));
rows.forEach(row => delete worksheet[row]);

// Rename the first column header to "name"
worksheet['A2'].v = 'name';

// Rename the second column header to "id"
worksheet['B2'].v = 'id';

// Convert the modified workbook to base64
const modifiedData = XLSX.write(workbook, { bookType: 'xlsx', type: 'base64' });

return modifiedData;

From there, you can pass the data from your JavaScript query directly to an S3 upload query:

Let me know if that works!