Facing errors while fetching results from excel sheet as Data base source
Question
Application Type
Mobile

Hi All,

I did the below steps while trying to write a SQL query in Outsystems:

1) I added the data to "Entities" by selecting "Import New entities from excel" and selected an excel(Credentials) containing login credentials.

2) I published the project

3) I went to the Data tab and selected this entity that was added

4) On double clicking this entity the data from the excel sheet was fetched

5) Once this was done, I reopened the excel sheet and made changes to the data

6) Again revisited the data Tab and selected the respective entity (Credentials) to query the latest data again

7) However, still the old data that was fetched is being displaced and not the revised data

Also, I did another step of creating a server action and adding a SQL to it. I did the test of running a SQL to query the data from this table. It also returned me the old data and not the latest data in the excel sheet. Any thoughts on why I am not getting the latest data. 


Hi JAYANT BHRARA,

Please check if the source you have bind for the binary conversion, It should be the output of your SQL query returning from the server action you are using also plz check the binary content u have bind with the download action.

Hope this helps....  

If still this is not the case please share the some screenshots to identify the issue. 

Hi Jayanth,
It seems that the update crud action does not run explicitly after updating the excel sheet. Please attach the OML file or screenshot of your logic so that it would be helpful to find the root cause.

Thanks.

Hi all,

There is no issue here, this is the normal working of bootstrapping based on excel file.

Your expectations that changes to the source excel will automagically find there way into your database are wrong.

In service studio you add the excel one time as a resource, and a timer runs once, only loading data in the entity when it is empty.

Nothing more !  So it is only good for loading an initial set of data.

You´ ll have to code your own logic to upload newer versions of the excel and process them into your database.

Dorine

HI Dorine,

Thanks, that does provide some clarity. So does it mean that incase the data in excel has changed, I need to delete the respective entity from my Database in "Data" tab under "Entities" and then I should reload that respective excel to get the latest data.

Or is there some way to refresh the data set from excel within Outsystems itself so as to get the latest version.  

Well, that's a bit too drastic, I think.  It really depends on the function of these data, but I think rarely the solution would be to just start over.  

Unless the excel is just a fast way to set up initial values when moving your app to the next environment, then we are talking about bootstrapping (what you have done here) and let's say that as a developer you made a mistake and want to start over, then that might be an option.


If we are talking about this excel being a part of your solution that is going to exist alongside the app you are building (i.e. somebody maintains this excel outside of your app and the changes need to be reflected in your app) then you will have to build several things

* logic to go pick up the excel

* logic to process it into your database (either empty the whole table and create from scratch, or typically it will have to be more subtle and carefull if your data have relationships to other parts of your datamodel, or for performance reasons if you have a very large amount of data and only a small percentage actually changed)

* a way to trigger above logic

So how you do this depends on how often that excel will change and how large it is

* if it is only occasionally changed and not a very large excel, by a user that can also access your app, you could build a screen for him/her, with functionality to upload the new excel, and just call a server action to process the excel

* if it changes very often and/or is too large, you could build a timer that runs every hour, accessing the excel on some location through for example ftp, to check if it has changed, and if so, process it

For how to write your logic, you could look at what your bootstrapping logic does (when bootstrapping, a server action is automatically created, you should find that in your module) but that is very simple, for starters, it doesn't do anything when your entity already has data, you definitely need to do something else, what that would be really depends on the complexities of your datamodel.

Dorine

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.