Upload Excel data into SQL SERVER using powerautomate (does not load all rows)

[SuGadgil]

Hello everyone,

I am new to Powerautomate.

I need to upload Excel data into a table on SQL SERVER.

I could create a flow with two step
1) List rows from Excel table
2) insert into SQL server

but it stopped after loading less than 100 rows. 

I need to load at least 8000 rows and the number of rows will grow each month.

I tried to update the setting for List rows ( to pull 10000 rows, enabled pagination)

but it does not load all records.

In addition how do I upload data in bulk to make it faster?

-Su

[gabibalaban]

@SuGadgil ,

Unfortunately, I don't think that you will be manage to retrieve more than 5000 items with only one Excel - List rows from a table action, so the best way is to somehow split the Excel table info using Filter Query option from this action in more than one part and execute repeated  Excel - List rows from a table action.

To make the insert in SQL faster, use an Apply to each action with the concurrency option set as you need (maximum 50 parallel actions) and inside it a SQL Insert row action. This will speed up the write process to SQL table. 

Hope it helps !

[SuGadgil]

Hello Gabibalaban,

so I need to read 5000 rows at a time (using list rows from table) and then for each of those, apply insert into db with concurrency of 50 or less.

let me try that.

-Su