Goal: I receive a monthly report in xlsx format. I want to build a dashbaord for the stakeholders that will use some of the data in the report. The person who prepares the xlsx report will import it to the dashboard using a file input component.
I tried converting it to GSheets but query options are limited compared to an actual database.
Details: I also need every new file to be saved with either a date tag in a new column or in a new table programatically named. This way I will be able to compare the values from different months on the dashboard.
I tried the cod ein the screenshot but didn't work for me.
On the file upload options, select the option to "Upload file to Retool Storage" and don't "Replace file with same name" if the file will always come with the same name and you want history.
On the file upload options, select "Parse files" option to parse the XLSX on upload.
Display the parsed data in a table / send the parsed data to your DB / etc.
File Uploader with said options:
Displaying the parsed data from a simple Excel file:
The original file looks like this:
Obviously this may not be sufficient for your needs.
Retool Storage is a straightforward built in solution, but has its limitations. If you need more than it offers, write an APIs query to send the file to your preferred storage solution.
The "Parse files" option for Excel isn't necessarily going to handle more complicated files the way you want. For example, if you are getting an Excel file meant for human consumption, it probably isn't just a table of data. Maybe it is something like this:
Which, when parsed, looks like this:
Still usable, but you can imagine it gets more complex as the number of sheets and formats expand.
If the simple, broad strokes approach doesn't work for you, your best bet might be to take the file from the input and send it to a workflow that performs the same basic steps but with more control and features, i.e., log the name of the file and some meta data to a DB table, then send the file to your storage solution, then use some Python to get the specific data you are looking from and manipulate it for your database, ending with a webhook that returns to the app whatever data from the XLSX file you are interested in analyzing or processing (or a DB id that the app can use in a query to get that data...).
