Excel Worksheets Data Import Asynchronously
Question
Application Type
Reactive
Service Studio Version
11.10.22 (Build 41778)

Hi All,

I wanted to import Excel data into entities Asynchronously.

I have Excel with 2 worksheets say Sheet1 and Sheet2. 

Sheet1 contains around 5K records having 40 columns. 

Sheet2 contains around 20K records having 30 columns. 

I want Sheet1 data to be in Entity1 and Sheet2 data to be in Entity2

My Main concern is how to store the worksheets data Asynchronously (by timers)?

.oml will be appreciated 

Thanks in Advance.....


Solution

I've created a quick example that you can use for inspiration. See attachment.

ExcelImport.oml

Hi Rohan,

You can definitely use a timer to import the rows from excel to records in OS. You may import the Excel file previous to the timer execution. 

For instance, the most easy approach is to add the excel as a resource of the module and when the timer executes just read from that excel file (this is the pattern generated by OS when populating an entity with data from an exceç, but SS schedules the timer execution to when module publication is finished).

Other approach could be to save the excel binary in an entity record and when running the timer just need or fetch it from the database.

Even though using timers is important, be aware of timer's timeout and if the importing process is taking almost the timeout time consider to split the importing among multiple timer executions.

Cheers,

António Pereira

Hi, @Rohan Jain .

You can use one of the two asynchronous mechanisms OutSystems gives you: BPT or Timers. For your problem, timer looks the right choice, as it gives you a bigger window for processing your excel files (20 mins standard timeout) vs the shorter BPT timeout (5min in normal BPT and 3min in Light BPT standard timeouts).

Timers:

1. Create a screen where you will upload your Excel files and immediately store the Binary file in the database. Do this in a screen action. To save your excel binary you can create one entity: ExcelImport (Id, BinaryFile, DateTime).

2. Create a timer and start by having an aggregate fetch the latest record from the ExcelImport table. Put a max records 1 on it so it does not fetch any unnecessary records from the database. If it founds one then continue (next point). If nothing is found then End.

3. In this timer, create your logic to process your excel data and store it in the database. Before the end of this timer action, do not forget to delete the ExcelImport record you just processed. Finally, wake the timer again because we might have some new ExcelImport data ready to be processed. Remember, we handle the stop of this timer by checking if records are found on the aggregate we created in point 2, so we don't end up in an infinite loop where we are always waking the timer.

4. In your screen action created in point 1, after you save your excel in the excel import tables, perform a Commit Transaction and then Wake the timer you created in points 2 and 3. Commit transaction is needed to guarantee that when the timer starts the ExcelImport data is already committed to the database.


Although your excel files are processed asynchronously, they are not processed in a parallel way (2 excel files processed at the same time), since the same timer cannot be parallelized. You could create another timer and additional logic to have two timers running in parallel, or actually use BPT. However, you would still have the database as a bottleneck as it would not be possible (virtually speaking) to parallelize the writes to the same table. To make sure the BPTs were not blocking one another, you would first read and have the excel data on a local variable ready to be bulk inserted in the database as soon as you finish handling all records (as described in the next tip).

Tips: inside your timer, instead of writing line by line to the database, try to build first a list in memory (local variable) and then perform bulk insert. It will speed up your code. You could even try to commit in batches if the memory consumption starts take a toll on your server, which by the numbers you gave it does not seem the case (assuming 20 chars per column).


Hope this points you in the right direction.


Best,

Tiago


Thanks @Tiago Pinho for the pointers you mentioned. If it is possible can you please share the .oml that will be help me a lot.


Solution

I've created a quick example that you can use for inspiration. See attachment.

ExcelImport.oml

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