Replace old data by importing new Entities from Excel...?

Hey together,

is it possible to import new data from excel in an existing app and replace the old data with the new?


I've created an app which is based on an excel import. The data inside excel was not up to date, I used it just for testing. Now my boss gave me an updated excel file with data which is up to date. How can I use it in my app? The structure of the new excel file is similar to the old one.

Or is that not possible? Do I have to create a new app?

I'm looking forward for your answers.

Thanks in advance

Best regards 

Tobias



just use an query to delete the old date


And use an action to loop the excel and create the new records to the table. (you can do the delete and add in the same action).

Then run that action (you can create a timer to run just one time and then delete the action)



BR

Hi Tobias,

You do not have to create another app.  You will have to create an action in your application to delete the data.

I can see two options for you.  Which one you choose depends on the requirements for your application.

1. One is to implement a button and action to delete data previously imported.

2. In the import, first delete all existing data.


1. Implement a button and action to delete data previously imported.

Add a button which calls a "DeleteAllData" action.

In the "DeleteAllData" action, to perform the delete you can either create an Aggregate to query for the data to delete, and use a for loop to delete each entity, one by one.  If you have alot of data, this will perform poorly.  So in this case you can use an Advanced SQL statement to delete all data in one SQL command.

2. In the import, first delete all existing data.

Create a "DeleteAllData" action as mentioned above.

In the import action, call this "DeleteAllData" action prior to adding any new records.

Though, the requirements for your application may be that this is not a feasible option for you.


I hope this helps!

Kind regards,

Stuart

Hi Carlos and Stuart,

thank you for your help.

I think I know what you mean but I need a little more help.


I tried a little bit with Stuarts first suggestion, like you see in the screenshot.

Is it nearly what you meant? The statement returns a "Query returned no rows" - Message. DELETE * FROM {Entity} doesn't work. My SQL skills are not so good...


For the second suggestion I have no idea what and how to do...


Would be nice to get more help and hints from you


Best regards

Tobias

hi!

" The statement returns a "Query returned no rows" - Message. DELETE * FROM {Entity} doesn't work." 

The delete statement don't return any rows, so if you don't receive more messages your SQL statement is good.

Testing it in service studio will not produce results after closing the window. To see if it's working I would use a "Select" statement in the end of the script that is erased after the test, but in your case as you are deleting all the data in the entities, you will get that same message anyway . 

Hope this help

Graça   

What you post here is a question about basic sql and not outsystems you could get the answer simply searching how to do a delete in google.

https://www.w3schools.com/sql/sql_delete.asp

The output parameters can be what ever you want since it is a dummy output parameter this query will not return anything only delete. (Make sure you delete the entities in order in case you have relations between tables and you need to delete more than one).


EDIT: You can short the query and use Delete {entity} where (condition)

Hi Tobias,

You are almost there.

Maria and Carlos are correct you shouldn't expect any rows to be returned.

In your output parameters you do not need to include the three entities.  However, the platform requires an output parameter, even though it will not be used.

I recommend creating a dummy structure called "Dummy" with a single attribute called Id, and then I use that for all my DELETE and UPDATE advanced queries.

Kind regards,

Stuart


Tobias Ihlein wrote:

Hi Carlos and Stuart,

thank you for your help.

I think I know what you mean but I need a little more help.


I tried a little bit with Stuarts first suggestion, like you see in the screenshot.

Is it nearly what you meant? The statement returns a "Query returned no rows" - Message. DELETE * FROM {Entity} doesn't work. My SQL skills are not so good...


For the second suggestion I have no idea what and how to do...


Would be nice to get more help and hints from you


Best regards

Tobias



Hey together,

first of all I thank you for your replies. Sorry about any dumb question...

Ok now my data is deleted, but what do I need to update it?

Carlos' suggestion was:


"And use an action to loop the excel and create the new records to the table. (you can do the delete and add in the same action).

Then run that action (you can create a timer to run just one time and then delete the action)"  


At first do I have to import my new excel to get the new entities? I think so, is that right?

What about the update action, what do I need exactly to create it???



It's a bit embarrassing for me to ask so many (dumb) questions. I'm right at the start to discover the possibilities of OutSystems...

Thank you all!!!

Kind regards 

Tobias

Hi Tobias!

As I understand from your question, the requirement is to insert updated data into the same structure that you already have used in building your app.

The easiest way to have your data loaded then is to edit the excel file you have received and match the column header names to exactly match the entity attribute names you use in your application. Make sure to rename the worksheet to match the entity name (not 100% sure this is strictly needed, but better to be safe than sorry). Once your Excel is prepared, you can right-click on the entity you wish to import and chose Advanced>>Create action to bootstrap data from Excel. This will give you the scaffolding to import the data with loops and everything. Based on this you can then adjust the behviour as needed. Be especially cateful with number columns with blank cells. Here is a guide on how to manage that.

I hope this helps.

P.S. If your boss is serious about Outsystems, you should ask him to allow you to spend a week doing the first level web developer online training. If he is hesitant, just tell him it's for free ;)