cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
barryburke
Level: Powered On

Excel to SharePoint - flow fails on update (date to integer)

Hello

 

I have a flow that compares an excel file with a sharepoint list and row by row determines if a new record should be created or an existing record be updated with new information.

 

I first ran into the issue of excel dates being an integer which i found could be solved with 

addDays('1899-12-30T00:00:00Z',int(item()?['Incident Date'])), but this was only used with the SharePoint 'create item'. Once i used this code on the sharepoint 'update item'.
 
If i delete all the items in the sharepoint list and run the flow, it completes fine and adds all of rows as needed. However if i run it again, it fails stating as per the screenshot
flow1.pngerror message
 
If i remove the date expression and leave the date field blank, the update process works fine. However i do not have an 'Incident Date' excel field available when i allocate matching fields for the create or update item. I feel 
 
The flow runs as follows:
  • Recurrence
  • Pull excel table and list rows present
  • apply to each excel 'value
    • get items from sharepoint list (matching via the unique id (UID ew 'UID') with top count of 1
    • condition to evaluate if the value is empty (empty(body('Get_items')?['value']))
      • if yes create new item in sharepoint
      • if no update item in sharepoint

Here is a screenshot of the final condition

flow2.png

 

Any ideas? as i mentioned earlier, if i remove the Incident Date expression from the update items, the flow runs fine. also if i remove all items from the sharepoint list (and each item needs to be created), the flow runs fine. There is something i am doing wrong with the date expression (which is identical to the 'create item' Incident date expression.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Excel to SharePoint - flow fails on update (date to integer)

Hi @barryburke ,

 

When using Excel Online connector, the date value will be returned as String, which could not be saved to other date and time format columns.

 

Please take a try with the following workaround.

 

In the Excel table, please format the Date column as Text first, then input the date in each field.

1.PNG

 

Then start the flow with the similar configuration with what you’ve provided.

 

When there are no existing items, it will create new item in the list. When there are existing items, it will update the items with corresponding value.

 

Images for your reference.

2.PNG3.PNG

 

By the way, when sharing images with us, please remove/hide your personal info including email addresses, organization names, user names, etc. for security purpose. Thanks for your understanding.

 

Best regards,

Mabel

 

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

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Excel to SharePoint - flow fails on update (date to integer)

Hi @barryburke ,

 

When using Excel Online connector, the date value will be returned as String, which could not be saved to other date and time format columns.

 

Please take a try with the following workaround.

 

In the Excel table, please format the Date column as Text first, then input the date in each field.

1.PNG

 

Then start the flow with the similar configuration with what you’ve provided.

 

When there are no existing items, it will create new item in the list. When there are existing items, it will update the items with corresponding value.

 

Images for your reference.

2.PNG3.PNG

 

By the way, when sharing images with us, please remove/hide your personal info including email addresses, organization names, user names, etc. for security purpose. Thanks for your understanding.

 

Best regards,

Mabel

 

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

View solution in original post

barryburke
Level: Powered On

Re: Excel to SharePoint - flow fails on update (date to integer)

Hi Mabel

 

Thanks for the tip, i didnt see my web adress was still there, i know for future.

 

I tried your method and found the date did show up in the list. Thank you

 

Barry

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
Users Online
Currently online: 344 members 5,955 guests
Please welcome our newest community members: