cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SuGadgil
New Member

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

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

2 REPLIES 2
gabibalaban
Super User
Super User

@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
New Member

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (3,067)