Importing Dates and Numbers from Excel file into SharePoint list with Power Automate
Importing Dates and Numbers from Excel file into SharePoint list with Power Automate
Transferring data from Excel to SharePoint lists can encounter format issues, especially with datetime and number fields. This post covers solutions to some challenges particularly related to dates and numbers.
The flow is quite simple with an action to read contents from excel file and an action to create items into a sharepoint list.
Handling DateTime Fields
When importing datetime data with the “List rows present in a table” action from the “Excel Online for Business” connector, you might encounter format related errors.
The error typically appears when the “Create Item” action in SharePoint fails due to the date format not being recognized:
Solution: Adjusting Date Format
The resolution is to adjust the setting on the Excel Online for Business action to interpret dates in the ISO8601 format.
Handling Number
Importing empty number/double columns can lead to failures with errors of type OpenApiOperationParameterTypeConversionFailed.
Solution: Handling Null Values
The fix is to add a condition for the Number/double field to cater for null values before importing the data.
if(empty(items('Foreach')?['Actual Total Cost']), null, items('Foreach')?['Actual Total Cost'])