How to migrate from excel to sharepoint list

[Murali_krishna_P]

Hi Team,

we have an excell around 15k records, we have tried to update using Power automate, but after writing 100 records its not perfomring any, seems like limitation.

Is there any way to update from excel to SP list, if yes please specify clearly tool name and version.

you reply would be appritiate.

thank you

[#2]

@Murali_krishna_P in your list rows present in a table action you need to go to settings, switch on the pagination toggle and set the threshold which is the number of rows to return, for example 15000. If you don't do this the action only gets 100 rows by default.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#3]

Thank you for reply.

I want to understand your answer means get 100 recrods first then write only those 100 records, next get another 100 and update repeate correct me if i am wrong.

[#4 · Accepted solution]

@Murali_krishna_P no, that's not what I said at all. Follow my instructions and enter the threshold as 15000 or whatever the number of rows is. That gets over the 100 row default. Then the flow will bring back all the rows and you can add the apply to each and create item action to add them them into SharePoint. It won't be quick as the flow has to process every row.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#5]

Okay thank you.

[#6]

@Murali_krishna_P 

You can also use VBA, code below needs to be modified to loop through your Excel

Sub NewSharePointItem()
'Reference to be added: Microsoft ActiveX Data Objects 6.0 Library
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
SQL = "select * from [NIFdb] ;"


With con
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=https://xyz.sharepoint.com/sites/site/;LIST={81e876da-1d06-4c9f-bc40-177e66b97db6};"
.Open
End With

rs.Open SQL, con, adOpenDynamic, adLockOptimistic

rs.AddNew
rs.Fields("Column1 Name here") = cell(1,1)
rs.Fields("Column2 Name here") = cell(1,2)

rs.Update
rs.Close


If CBool(rs.State And adStateOpen) = True Then rs.Close
Set rs = Nothing
If CBool(con.State And adStateOpen) = True Then con.Close
Set con = Nothing

End Sub