cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gavinleung
Level 8

Import Excel data to an existing Sharepoint List

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

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Import Excel data to an existing Sharepoint List

Hi @gavinleung 

 

Please see the screenshot below: asd.PNG

 

 

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!

View solution in original post

10 REPLIES 10
Dual Super User
Dual Super User

Re: Import Excel data to an existing Sharepoint List

Hi @gavinleung 

 

Please see the screenshot below: asd.PNG

 

 

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!

View solution in original post

gavinleung
Level 8

Re: Import Excel data to an existing Sharepoint List

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 ? 

Dual Super User
Dual Super User

Re: Import Excel data to an existing Sharepoint List

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
Level 8

Re: Import Excel data to an existing Sharepoint List

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 ? 

Dual Super User
Dual Super User

Re: Import Excel data to an existing Sharepoint List

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!

Community Support Team
Community Support Team

Re: Import Excel data to an existing Sharepoint List

 

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,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gavinleung
Level 8

Re: Import Excel data to an existing Sharepoint List

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

Dual Super User
Dual Super User

Re: Import Excel data to an existing Sharepoint List

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
Level 8

Re: Import Excel data to an existing Sharepoint List

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
Level 8

Re: Import Excel data to an existing Sharepoint List

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,781)