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

[barryburke]

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

error 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

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.

[v-yamao-msft · Accepted solution]

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.

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.

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

[barryburke]

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