When Excel first row has blank date all remaining row dates will be Serial instead of ISO 8601

[wskinnermctc]

Excel Dates Changed to Serial Instead of ISO 8601 Due to First Row In Table Blank

I have been using the API Batch upload method to get data out of an excel table and into a SharePoint list. I was having an issue with the date conversion after the List Rows in Excel Table being correctly set to ISO 8601.

I did some testing, and it seems like if the first row of the table has a blank in the date column then it will cause the entire column to read and converted to Serial.

I can't guarantee that the first row in my data will always be filled in the date column. Why does Power Automate not recognize the rest of the fields in the same column with the ISO 8601 setting?

I’m using the Batch Create SharePoint list items Batch Update SharePoint List With External Data  , and do not have an issue with it working. I have made it work and have it running with Excel and Dataverse tables. I just noticed this date issue and do not understand why the order of the dates in the excel table impact how Power Automate reads the column even with the setting as ISO 8601.

The first test I had all date fields filled and the date was converted to UTC as power automate normally does.

The second test I deleted the date fields from the top row in two of the columns and it made the remaining rows Serial.

Example 1 - All Date Fields Filled

The source Excel table is a list of 5 employees with 3 different date columns.

Excel Source Example 1 - All Dates

All of the rows are completely filled in and the flow to batch generate and upload runs without an issue. I'm attaching outputs of the test flow in the two steps of the flow "Generate SPData" and "SendBatch". The Generate SPData step is an array that combines all of the data. The SendBatch is the actual Send HTTP Request to Sharepoint that is what posts the data to the SharePoint list. The array is easier to read since it shows what the data is that is being sent within the batch upload. The array is where you can see the formatting of the datetime values. The outputs of the BatchUpload does not give a lot of detail, but it does show if the posting was successful via a status code.

Example 1 Results Generate SPData

All of the datetime fields in the Generate SPData are in the UTC format that was done by the settings in the Get List Rows step.

Example 1 Results BatchUploadExample 1 Results SharePoint List

The BatchUpload outputs are difficult to read, but it does show all 5 of the items were successfully posted. The SharePoint List contains all of the items and date fields when the flow is completed.

Example 2 - Delete Fields in First Row of Excel table

I used the exact same table and flow as the first example but the only difference is I deleted the dates out of the first row for 2 of the 3 datetime columns.

Example 2 Excel Source Table

None of the rows are successful in uploading from the flow because all of the dates are converted to Serial instead of ISO 8601 due to the first row being blank. I'm attaching the outputs of the Generate SPData and it clearly shows the first row has "" for the blank datetime columns and the last column that was filled is in the UTC format from the Get List settings ISO 8601. 

Looking further down the outputs the rest of the dates are now in Serial number format.

Example 2 Results Generate SPData

All of the rows failed to upload in the batch update (the first row failed because the blanks can't be sent to SharePoint in a datetime field). The last 4 rows failed because they were in Serial format that can't be sent to SharePoint. The output of the SendBatch shows the errors for each item.

• Error:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,
• Message{“value”: “Cannot convert a primitive value to the expected type ‘Edm.DateTime’. See the inner exception for more details.”
• StatusCode: 400

Example 2 Results SendBatch

Other Attempts:

I tried this while using a if(empty(null expression to replace the first blank row with null instead of "" and all the remaining dates were still in Serial format. I also tried a more standard Apply to Each and Create Item in SharePoint instead of the batch upload and none of the items were created since the dates were still in Serial format. (The error message for apply to each was Status 400 "

String was not recognized as a valid DateTime").

Conclusion:

The order of the dates in the Excel table seem to impact how Power Automate reads the type of the column even when the setting is set to ISO 8601. If the first row is blank in the date time field then the remaining rows are read as Serial.

If you have any suggestions or ideas about why this happens please let me know.

Batch Upload Reference:

The steps I am using came from @takolota and his instructions and video. They have been a big help! I'm posting pictures of the overall flow here for reference to my flow on this post.

Power Automate Cookbook Batch Update SharePoint List With External Data 

Tachytelic.net 2021/06 Power Automate Flow Batch Create Sharepoint List Items 

Batch Upload Flow OverviewBatch Upload Flow Detail p1 of 4Batch Upload Flow Detail p2 of 4Batch Upload Flow Detail p3 of 4Batch Upload Flow Detail p4 of 4

[takolota]

@wskinnermctc 

Thanks for the shoutout & feedback.

My first guess for the issue is that if the Excel rows are blank and dates, then maybe it is registering it as a text column instead of a date column, so the ISO 8601 isn’t being applied.

Have you tried using a method like this to convert the date numbers to the date format you need in the GenerateSPData action?

https://www.tachytelic.net/2020/11/convert-excel-dates-power-automate/?amp

[wskinnermctc · Accepted solution]

I have a couple of correction steps for when the dates convert to Serial due to first row blank. I'll use expressions to convert the date numbers like @takolota suggested. However, the correction steps lead to a follow-up problem that requires additional changes to the overall datetime format of the Get List Rows step. 

I realize these correction steps aren't anything new and there are dozens of pages referring to these; however, I am going to have a problem in another flow and I want to be able to reference this post.

Correction Step 1 - Replace blank date fields with 'null' by using expression If(empty([Date]),null,[Date])

The first issue is that SharePoint can’t accept double quotes blank value for a datetime field. SharePoint can accept a blank so long as it has the word null to represent the blank data field. So using the empty() expression will check if the field is double quote “” blank and return a result of true if it is.

Combine the empty() expression with the if() expression to say If the date is empty then return ‘null’ else just return the date field.

if(empty(item?['Birth Date']),null,item()?['Birth Date])

Correction Step 1 Detail Generate SPData

I’m attaching a screenshots of the results of a test flow showing the inputs and outputs of the step Generate SPData. When running the flow using the if(empty() expression we can see that the blank field in Birth Date column was changed to null. The column CSVReportLastUpdate which did not have the if(empty() expression remained with the “” double quotes blank.

Correction Step 1 Results GenerateSPData

The problem with just using the if(empty() expression is that the remaining date fields after the first row are still in Serial format. None of the rows will upload to SharePoint because they are still in Serial and will not be accepted.

Correction Step 2 – Use addSeconds() expression to convert Serial dates

To convert the Serial dates to a format that can be used by SharePoint the expression addDays() or addSeconds() has to be used. There are many posts on converting dates that can be found. For my example I’m going to use the addSeconds() expression for both columns [‘Birth Date’] and [‘CSVReportLastUpdate’] since one column includes time and a bonus is the final result will be in a format that can be accepted by SharePoint without writing it out.

The expression addSeconds() is adding the number of seconds to the date 1899-12-30 based on the Serial date and then formats it. If no additional formatting such as yyyy-mm-dd is added to the addSeconds() expression it will default to a ISO 8601 format that is readable by SharePoint so I don’t add any additional formatting.

addSeconds('1899-12-30',int(formatNumber(mul(float(item()?['CSVReportLastUpdate']),86400),'0')))

Correction Step 2 Generate SPData

There is an issue with only using the addSeconds() expression due to blank values. The addSeconds() is unable to deal with the blank values and sends a flow error message when testing. The flow fails because of the error “The template language function ‘float’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.”

Correction Step 2 Results Generate SPData

Correction Step 3 - Combine the previous correction steps into a single expression 

Since the If(empty() expression is able to deal with the first row blank and the AddSeconds() is able to deal with the dates being in Serial format then put both expressions within an If() expression.

The expression will check if the field is empty then replace with null, and if the field is not empty then use the addSeconds to format the time correctly.

if(empty(item()?['Birth Date']),null,addSeconds('1899-12-30',int(formatNumber(mul(float(item()?['Birth Date']),86400),'0'))))

Correction Step 3 Detail Generate SPData

After putting in the expressions and running the test flow I’m comparing the Generate SPData inputs and outputs to see the changes the expressions made. Since the dates are now formatted as IS0 8601 they can be uploaded in the SendBatch and see the result is they are in the SharePoint list correctly.

Correction Step 3 Results Generate SPData

Excel Source Table Top Row Blank FieldsSP List Upload Results after Step 3

This solution is only temporary and applies specifically to this example with dates blank in the first row. Since the blanks in the first row changed the remaining rows to Serial they had to be corrected with the expressions. If the first rows are not blank, the expressions will cause errors.

Follow-Up Issue after Correction Steps:

The format of the Excel Get List settings is still IS0 8601, so if this flow is run again but the first rows do have dates filled instead of blank like the examples, then the flow will error since the addseconds() formula is attempting to add time to values that are already in the UTC format.

If I remove two date fields that are not in the top row the flow goes back to the correct ISO 8601 format that is UTC. In the results it shows the dates of Index 1 correctly formatted as UTC so the addSeconds() expression errors since the now UTC formatted date doesn’t work correctly in the float() due to not being a decimal number.

Follow Up Issue Excel Source table

I'm attaching a screenshot of the results after I remove the 2 dates but leave the expressions the same as before from Correction Step 3.

FollowUp Issue Result Error

The solution to correct this will be to change the List Rows dropdown DateTime Format to Serial Number which will change all dates to Serial format. This will allow the dates to work with the correction expressions whenever the top row has blanks or filled.

FollowUp Issue List Rows Setting Change

However, now ALL date column have to include the expressions since they will be in Serial format even if there are no blanks. If a column is not formatted from the expression it will not upload properly to SharePoint because it is now being pulled from Excel as Serial due to the List Rows settings drop down.

I ran the flow to get a copy of the results to show the formatting differences after changing the dropdown to Serial instead of ISO 8601. The flow ran, but nothing was uploaded to SP due to the last datetime column not having any expression changing it.

FollowUpIssue Result after Serial Change

So I have to go to the third datetime column CSVRptCentralStandardTime and also put it in an expression even though it hasn’t been an issue with any of the previous examples.

FollowUpIssue Results All DateFields Expression

This will upload properly to the SharePoint.

FollowUpIssue SharePoint List Result

Conclusion:

I'll probably never have an exact answer for why top blank rows cause remaining rows to be Serial. I believe @takolota is most likely correct with the import making an initial reading and then applying to the rest of the rows.

The solution for dealing with blank fields in the first row isn't exactly straightforward and depends on expected data coming in the fields. However, changing the dropdown to Serial instead of ISO 8601, then using expressions on all date or datetime columns seems the be the biggest catchall since it will account for blanks in the first row as well as format date correctly even if the first row fields are filled.