i am searching for a way to upload an Excel File and show it in a Table Widget. And after that create an MSSql Table from the shown table.
I already fail at the first step. The Excel data from the filepicker are obviously not suitable for displaying them in the table. Is there a possibility here ?
thank you in advance
Hi Franz, welcome!
Do you want to directly display the uploaded Excel sheet in a table? It’s not possible in Appsmith today the way you describe it. It is an interesting idea for sure and I’ve created an issue: [Feature]: Allow users to upload and view excel sheet without a DB integration · Issue #11182 · appsmithorg/appsmith · GitHub
To make it work today, you’ll need to first upload the file to a DB, convert it into JSON or an array, create a query to fetch the response, and then display the response in a table. Is it possible for you to first upload the file somewhere to convert it?
thank you for the quick reply. Ok i will try to Upload to Google Sheets. Probably that is an way ?
Yes you are right, that is a much simpler way Just for my understanding, do you use any other database?
i am using mssql. I want to build an app to import and manipulate the Excel Sheet.
@franz I have a possible workaround you can use with CSV files. You can fork the app below and see how it works
One more possibility related to Nikhil’s suggestion is to convert your excel to a google sheet.
Advantage of doing this is that Appsmith today offers Google Sheets as a datasource. You can connect your appsmith app to your sheet and set up the two way communication using queries.
@Nikhil Thank you. I want to look at the settings behind. But, is there a way to fork the app from cloud Version to my On Premise Server ? I
Yes you can fork it first to cloud and then export it. Then you can import it to your on Prem
This is amazing thank you. Has anyone on the team taken this to the next step and implemented validation?
A basic check that data loaded from CSV into a staging table complies with validation rules (Data type (e.g.: integer, float, string), Range (e.g.: number between 35-40), Uniqueness (ex. Postal code), Regional phone number in correct format, Consistent expressions (ex. Using one of St., Str, Street), No null values, etc etc).
Ultimately we want to prevent submission of bad data before it gets to the database.
Hey @MagnumPI you have to ideally write rules on top of the data and highlight cells which have errors. While we don’t have a demo for it, it should be straightforward if you use a JS object to write a custom validation function and conditionally change colors of cells which have problems.
Do let us know if you need any help building this and we will assist you.