Bulk Upload of Excel Data to Outsystems Database

Hi Guys,

I had a scenario where am getting 5 to 10 Lakhs of Records with max 200 Columns.

Each time the user uploads the excel file we need to Delete the old records which are loaded earlier and load the records to outsystems database Table(table is already created).

Problem here is am facing timeout Issue.

1)Can any one suggest to overcome this time out Issue.

2)Can we load only few Columns from Excel to Database table,So that we can split the huge table into small Sub tables

Thanks in Advance

1) just upload the file bulkly (no need to extract it to records)

2) after uploaded the bulk data, then run a job to extract to records.

Hello  Cuan,

Yes we can upload file and store in Binary but my scenario demand the Values(Distinct Values in DropDown) of Corresponding Columns(Selected in DropDown)

Scenario:After Upload,The User Selects one Column from Dropdown(All Columns from the file),Once Selected the Values of Corresponding Column need to displayed in Dropdown(Distinct Values)

Can you please suggest me any way we can achieve this.


HI Sasidhar ,


To support such uploads and huge data processing Outsystems has Timers which runs in the background and perform such operations.

What you need to do is , create a time and write the entire logic of data processing there . When your user will upload file and its completed wake up the timer to process this recently uploaded file.You may need to create few additional entities where you can keep the information about what file needs to be processed by timer.

keep one thing in mind Timer also has timeout and you need to either increase the default timeout of the timer or to design the timer in a way that it should deal with the timeout and reinitiate itself.

Here are few link which can help you designing a Timer with recommendations.

https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Timers

https://www.outsystems.com/learn/courses/43/master-class-on-best-practices-and-timers/?StartCourse=False

I hope this will help you understanding the usages of Timer in Outsystems.


Regards,

-PJ-

But Pramod, We need to display the Values in Dropdown once the user selects the column.

If we use the timer it will be time consuming and user had to wait for huge time depending on the Data which end user wont accept.

I would like to know any way where I can display the values in Dropdown.


More over its Global Application where multiple users uploads the files in nearby short span of time.

But all these files will be loaded to the Same Database table.Also We need to delete the old data before loading the Excel Data.Because the each plant requires only their Current Excel Data only

# Once the binary extracted to records/rows, the rows can be retrieved.

# The values of the dropdown / drop1 ( to differentiate it with the values_droptdown/drop2) must be populated from advance query:

select "col1", "col1"

union 

select "col2", "col2"

union...

# in the callback action of the combowidget of drop1, OnChange, do ajax refresh to the drop2 widget.

# the drop2 has unique values that is returned from a query, try:

## create a function that returns a list of records (or list of structure). The result return will be the distinct values of drop2. Assoicate it with the drop2.

## put input parameter to the function that has text type, which has value column name to retrieve (from the drop1 value, say it "col1", "col2", etc.

## do advance query to the entity/table, where the query is like this: select col1 from entity 


# I am doing the same project in a banking now, to upload bulky excel that has time out risk.

# so i need to do like i tell above, and it is working.

# some issues, in my project is concurrency, where multiple users can upload at the same time, and the drawback of job that has no input parameter. I manage this by using lock.

Am Confused,Can you share me the oml file with sample bulk upload

The Cuan wrote:

# Once the binary extracted to records/rows, the rows can be retrieved.

# The values of the dropdown / drop1 ( to differentiate it with the values_droptdown/drop2) must be populated from advance query:

select "col1", "col1"

union 

select "col2", "col2"

union...

# in the callback action of the combowidget of drop1, OnChange, do ajax refresh to the drop2 widget.

# the drop2 has unique values that is returned from a query, try:

## create a function that returns a list of records (or list of structure). The result return will be the distinct values of drop2. Assoicate it with the drop2.

## put input parameter to the function that has text type, which has value column name to retrieve (from the drop1 value, say it "col1", "col2", etc.

## do advance query to the entity/table, where the query is like this: select col1 from entity 




Hey,

Have you tried to use the bulk insert action from the forge? 

https://www.outsystems.com/forge/component-overview/1117/bulkinsert

You may bootstrap the data for a temporary table and then manipulate the info as you wish

sorry, i cannot send the oml because it is confidential.

which one do u confuse?