Bootstrap Data and Entities from Excel programmatically
53
Views
17
Comments
New
Data & Integrations

Hi,

I worked on Data Migration of an Application from Appsheet to Outsystems, and it took a while.

Data need to be updated so I used a timer.
However, if there was the possibility to bootstrap Data and Entities from Excel programmatically, this would make it much simpler.

I think this use case is vey often, I mean data in Excel and it could be updated, so if the bootstrap can be done programmatically, people can just get the process much easier.

Kind regards,
Sirajeddine

2020-04-15 12-37-36
Daniël Kuhlmann
mvp_badge
MVP

Hi,

Why you think it is not possible, you can program this bootstrapping, you can even use OutSystems to generate the basic flow for you and then adapt to your needs.

Regards,

Daniel

Hi Daniel, could you please tell me how, I have read somewhere that this bootstrapping can not made programmatically and thus could not be automated

Hi @Sirajeddine Bouasker,

In the Data tab of the module you want to have your entities, you just need to right-click on Database and choose the "Import New Entities from Excel..." option. Then, you just need to select an Excel and follow the indications that Service Studio prompts to you.

Hope it helps.


Regards!

@Ricardo Reis
Thanks, I know this and I have used this already but I am talking about automation of this process, no drag and drop, no manual intervention

Ok, Sirajeddine.

However, I'm sorry, but I still don't understand how you envision how your idea could be implemented in Service Studio, and how it would be faster than the current feature I mentioned in my previous comment.

Can you please provide more details?


Regards.

Sometimes we need data update behind the scenes, I mean programmatically, without the need to drag and drop every time, we just make a timer for example, then it checks for updates (changes in comparison to last version of Excel file) ,if any changes occurred, it delete old Excel and bootstrap the new one. This is a sample use case.

Ok, I think I see what you mean now.

In my opinion, you could create a simple back-office application, where you'd have an Upload widget to upload your Excel file. Then, behind-the-scenes, you would have logic that processes that file and creates and/or updates what you need. That could be implemented in a timer or Light BPT process that the Submit button would trigger.

That way, you would only need to update a new version of the Excel file and click a Submit button every time you need to update your data model, as long as your file keeps respecting a template you define and your logic is prepared to process.

Nevertheless, the logic under-the-hood would always be tailored to your specific needs, and not something generic Service Studio could offer off-the-shelf.

What do you think?


Regards.

Sounds that you understood my idea, still it is feasible and if Outsystems add this feature, I am sure it would be very helpful for many Excel users.

As you may understand, every application's data model in the world is unique and different from one another. 

The only way I think there could be a generic solution that works for everything would be Service Studio to provide a template Excel file that everyone would need to follow. You would just need to fill in that file with the entities and data you want to store in the database.

Sorry Ricardo, this has nothing to do with Data Model
It is all about automation of Excel file import (in stead of drag and drop), regardless the content (entities and data) inside the Excel file itself.
It is all about programmatically updating the existent data and entities by the new data if same entities are there (same structures) or replace the old  data and/or entities  with the new data and/or entities accordingly
This process doesn't depend on data Model, but it will compare the 2 versions of Excel file, and do the update(s) if necessary.

Regards,
Sirajeddine

Sirajeddine,

A data model is composed of entities, so I mentioned "data model" as a synonym of entities.šŸ™‚


Regards.

I think it would be nice actually.


@Ricardo Reis @Daniƫl Kuhlmann
 
The idea came from the fact that if we are using Google Sheets then we have to download the Excel the drag and drop it to bootstrap Entities see the pic below:


Which is in the Outsystems documentation here
We can automate  the process in the Google side for downloading the file, but we need also to automate it in the Outsystems side for bootstrapping, and for uploading it back to Google Sheets to keep data updated if we made changes on Outsystems side and we want to keep them and translate them to the Google Sheets side.
I hope this makes my idea clearer.

Kind regards,
Sirajeddine



Hi Sirajeddine,

That indeed makes your idea much easier to understand. Thanks. šŸ™‚

To integrate with Google Sheets, I suggest you take a look at the Google Sheets Integration Service Forge component. It's a Trusted component, so it may help you with your use case properly.


Regards!

Thanks Ricardo I have used it, and it doesn't work 100% I have implemented some updates to make it work, still no automation of upload/download to avoid drag and drop

I am little bit confused, are you talking about Excel files or Google sheets, or both?

It should be for both, but namely for Excel files, because even Google sheets should be downloaded as Excel files then dragged and dropped to bootstrap Entities currently