Select from database and perform operation in chunks for all item

Help Forum
1

Hi devs, when I select all rows from database using query, I get memory exception, please can someone give an idea or solution on how to select in chunks till all the rows are processed.Thanks

2

Hi @RPA-botDev

Usually taking data as chunks/batches will help. We have something similar in UiPath Marketplace.

Please try this

Hope this will be useful. Thank you.

3

@RPA-botDev

Also check below, for your reference

Hope this help you

Thanks

4

Hi Jobin, this is also helpful, but in his case, he has already read the csv file before writing in chunks, but in my case I need to read or select the data in chunks then also write in chunks.Cause when I read all the data at once I get memory exception

5

which database system is used? How in detail are the rows are fetched (DataTable Activities?)

6

Hi @RPA-botDev

You first need to know the rows count. You can do this with a VB.Net or C# Code for Excel.
After that you can do a calculation for example: RowsCount / 4
For example Rows Count = 100
→ Take RowsCount / 4 = 25 → Read only the first 25 rows. Do everything, then go back and do the same for the next 25 rows until you are done with all rows.
To proceed the items fast use LINQ queries.

Video about connecting with Excel in Invoke Code activity:

Statement to get the rows count:
→ 'Get Rows Count
Dim RowsCount As Int32 = xlWorksheet.UsedRange.Rows.Count

You can watch some videos in my channel to learn how to get the rows count using VB.Net Code or C# Code. You can also learn how to work with LINQ.

LINQ Training Video:

There are also another videos about LINQ for the different functions in LINQ and how to do something there.

Link for YouTube Channel:

1 Like
7

It’s sql database, I’m using Select * from table but need to select per 1000000 rows and perform operation then process the other rows depending on row count

8

Hi Dawodm, I have gotten the row count but please can you share the linq to go back and process the rest of the rows till the 100 rows are processed.

9

You need the following:
First add Assign for new variable: Counter = 0
Then While Loop with:
→ Condition: While Counter < RowsCount
→ Then Your activities, what you want to do.
→ Merge tables to merge the out table with the proceeded table in the loop (if you need it)
→ Then Assign: Counter = Counter + 25

1 Like
10

Okay, I will, thanks