Import Excel data to an existing Sharepoint List

[gavinleung]

Hi all, 

There is an existing Sharepoit List, which was source as the data source of my PowerApps. The app is completed, and it's now the time to import production data from an Excel table to the Sharepoint list. 

The Import Spreadsheet app on Sharepoint required me to create a new Sharepoint List, which couldn't be the way I want. So I'm planning to use a Flow to automate this. Could anyone help to provide me some guidance on like retrieving data from the Excel, columns mapping, data type mapping (I have a Person column in Sharepoint), and to import into Sharepoint List ? 

Thanks,

Gavin

[yashag2255 · Accepted solution]

Hi @gavinleung 

Please see the screenshot below:

Here, I am using the list rows present in a table action to get the rows from the excel table. (This action gets you only 256 rows so please change the top count to the number of rows that you want to get from the excel). 

The person field can be updated using the email address of the users (provided they exist in the O365 users in the organization) and you need to point the relevant excel column to that particular persons column in the action (tPersons is my person type column in sharepoint and Column4 contains the email ID's of the folks)

Similarly you can point to the columns as per your datasheet to excel and sharepoint list. Note, if you have choice type columns, you need to update the choice value in the create item action and point to the excel column that contains the choice label. 

For multiple select persons or choice type fields, you will have to create arrays and pass it in the create item action. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[gavinleung]

Thanks @yashag2255 . I tried but one major problem. There is a date field in the Excel file, with Date format. When Flow reads it, it becomes a number, eg 7/1/2019 becomes 43467. Any idea ? 

[yashag2255]

Hi @gavinleung 

Yep. This is a very known issue with the excel connector and one of the solutions for this is to convert the data type of teh excel column manually. You need to go to the excel sheet and select the entire column and change the datatype from date/time to simple text. In flow then you can directly point that to the sharepoint column in the create item action. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[gavinleung]

Just done that. But the value of the entire Excel column became number ... eg 43467. There are thousands of records, I just couldn't modify it. Any workaround ? 

[yashag2255]

Hey @gavinleung 

Okay! I found this solved thread in reagrd to the issue you are facing: https://powerusers.microsoft.com/t5/Building-Flows/Excel-dates-turned-into-integers/td-p/130364

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-bacao-msft]

Hi @gavinleung ,

This is a similar issue that I have replied before, please check it and see if it helps:

https://powerusers.microsoft.com/t5/Building-Flows/Excel-file-to-list-flow-date-issue/m-p/279258#M28...

Best Regards,

[gavinleung]

Thanks @v-bacao-msft @yashag2255 , what about the Person field in Sharepoint List ? 

[yashag2255]

Hey @gavinleung 

You can directly pass the email address of the person on the claims input type on the action and it would update the list accordingly (the person should be in the same organization for this and the email address should be present in the O365 users list)

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[gavinleung]

Thanks @yashag2255 ! I can update the list without error. 

But in the Person field, the existing records are using the Display Name, but the new records are using Email address. Any idea to align them into the same format ? 

[gavinleung]

Hi @yashag2255 , I used Users() and upn to match, then put DisplayName into the Person field of the Sharepoint List. And it works ! 

[Anonymous]

I've changed the top count to 100000 but it still only gives me 256 rows, what can I do to fix this issue? 

Tried it the second time and now it gives me 508 rows. In my file I have 66.000 rows but I am able to take it down to 8000 if it takes too long. 

https://gyazo.com/a19820e22282d26716dd8c0af9d16534

My flow to delete items in my sharepoint list also stoos after 5 minutes, how to fix?

[AriesVI]

@yashag2255 

I'm trying to figure out how/where you created this workflow to import the excel data into a list.

Was it from the list or the excel file?

[josephspengler]

Ok, I figured out how to make this work. The previous solution did not work for me. When I tried to list rows present in table, it would timeout trying to pull up the table name. I was trying to pull in 6000 records, but even if I reduced the table to 500 records it kept timing out.

But it inspired me to a different solution. I am going from excel, to a new sharepoint list, to the existing sharepoint list.

To go from excel to a new sharepoint list, use this workflow: https://support.microsoft.com/en-us/office/export-an-excel-table-to-sharepoint-974544f9-94bc-4aa8-91...

It works fantastically, and is reasonable fast. I've done about 20,000 records in 10 minutes.

But that workflow only works when creating a NEW sharepoint list. So in this case step 2 is moving from one list to another. So create a flow (I recommend scheduled, more on that later). Have it "Get Items" from sharepoint from your new import list. No need to filter or sort at all, you're eventually grabbing them all. By default this maxes out at 100 records, but you can click the ... go to setting, turn on pagination, and change the threshold. I did 500 at a time, I wouldn't recommend more than 1000 at a time.

Then do the sharepoint actions: Create Item, and Delete Item. Both of those will end up inside of Apply to Each. In create Item you'll have to setup the field mapping once, but it should be pretty straight forward.

Once you've built it, run it once. Based on the number of columns it might take a different amount of time to run. For me, 500 records took about 10 minutes. So set up the flow to run every 15 minutes. It will do another 500 every 15 minutes until it's done.

[Powerplatform01]

@gavinleung 

You can refer this to resolve your issue.Also request you to please subscribe this channel

https://youtu.be/NLEMPn-T3ns

[Anonymous]

How can i do it in Power Automate desktop. I did it office power automate but i didnt find the action in power automate desktop