Answered

Import Excel data into an existing SharePoint list

(1) Share

Report

Posted on by KS-20021558-0

I’m creating SharePoint list items from an Excel table via List rows present in a table → Apply to each → Create item. One SharePoint column is a Date (or Date & Time). Many rows in Excel have this cell blank, and my flow failed with:

Action 'Create_item' failed… Input parameter 'item/field_XX' is required to be of type 'String/date-time'. The runtime value '""' to be converted doesn't have the expected format 'String/date-time'.

SharePoint Date columns require a valid ISO string (e.g., yyyy-MM-dd or yyyy-MM-ddTHH:mm:ssZ). An empty string ("") is not valid — you must send null for blanks.

The following is my flow. Where am I going wrong and could anyone suggest how I can amend this flow to handle blank entries in the spreadsheet?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

Michael E. Gernaey 53,969 Moderator on at

Like (1)

Report
Copy link

Link copied!

HI @KS-20021558-0

A couple of things

1) I would need to see your Excel file, as according to the error you are trying to assign a NULL value (or blank) to a Date/Time column in SharePoint, which is not valid, at least not how you are doing it. So can you please share the data and validate which columns you have null values in and what the Column value type is in Excel and what is it in SharePoint

2) Date/Times are very annoying in Excel as it translates to SharePoint, you have to make sure that its proper ISO 8601 or whatever the number is I get it backwards every time, to be compatible. "" of course are not compatible as its "", but you also need to make sure under advanced for Excel you are checking the ISO checkbox to make it compatible

Lastly, what it simply means is, you have a String Column in Excel. It is perspectively holding what would look like is a string, but where you have blanks a string blank looks like this "" (no spaces) and Create Item is failing because it cannot convert "" to a DateTime.

It is telling you, you have to check if the String value is equal to "" and install pass the value null (which is an expression that tells SharePoint, to leave the DateTime blank) instead of trying to get SharePoint to convert "" to a DateTime

So we need to check if the DateTime value in Excel is a blank string and if so pass in null, instead of passing in the blank.

We can use an expression called coalesce, which will check if the value is black or null. If not it will return the DateTime string if it is, it will return null and make Create Item happy.

Like this

coalesce(items('Apply_to_each')?['ColumnName'], null)

In my examples I have an Apply to each, so that is the first parameter I am using. In your case, you would use the expression you are currently using in your DateTime column in Create Item instead.

This way it will return null if your excel column is blank "" or the value if not. Please remember, change this items('Apply_to_each')?['ColumnName'] to be whatever you have in your current DateTime column in Create Item

If these suggestions help resolve your issue, Please consider Marking the answer as such and also maybe a like.

Thank you!
Sincerely, Michael Gernaey

Was this reply helpful? Yes No
Suggested answer

Pstork1 69,125 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

In the Create Item action use the function tab of the dynamic content dialog to add an IF() function to test whether the date field you are adding is blank. If it is then have it use a default date or the null function. If its not have it use the date. It will look something like this

If(Items('Apply_to_each')?['datefield'] = "",null,Item()?['datefield']

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No

KS-20021558-0

41 on at

Like (0)

Report

Copy link

Link copied!

Thank you both @Pstork1 and @Michael E. Gernaey. I've attached the sample spreadsheet I'm testing the flow with. The actual spreadsheet is 20K long hence the need to automate the import.

The spreadsheet was created from an export from an Access database.

Publisher

Series Title

Series Number

Document Name

Document details

Document Price

Currency

Product

Supplement

Copyright Requested

Copyright Refused

Agreement Received

Reapply

Date ordered

Date received

Order number

Archive

Notes

Requested By

Priority

Date

ISBN

Public Private Partnerships Programme (4Ps)

Housing Toolkit

Pounds £

CIS

Management

2003-11-28 00:00:00

Michael has this doc

IST Team

FALSE

Public Private Partnerships Programme (4Ps)

Housing Guidance Update

Pounds £

CIS

Management

2003-11-28 00:00:00

Michael has this doc

IST Team

FALSE

Public Private Partnerships Programme (4Ps)

Guidance on Standardisation of PFI in HRA Housing Contracts

Pounds £

CIS

Management

2003-11-28 00:00:00

Michael has this doc

IST Team

FALSE

Public Private Partnerships Programme (4Ps)

Achieving Quality in Local Authority Building Projects

Pounds £

CIS

Management

2003-11-28 00:00:00

Michael has this doc

IST Team

FALSE

Public Private Partnerships Programme (4Ps)

Case study

Brent Street Lighting

Pounds £

CIS

Management

2003-11-28 00:00:00

Michael has this doc

IST Team

FALSE

Public Private Partnerships Programme (4Ps)

Case study

Derby Non-HRA Housing Case Study

Pounds £

CIS

Management

2003-11-28 00:00:00

Michael has this doc

IST Team

FALSE

Public Private Partnerships Programme (4Ps)

Case study

Dorset Police Authority (Western Division) PFI Project

Pounds £

CIS

Management

2003-11-28 00:00:00

Michael has this doc

IST Team

FALSE

Public Private Partnerships Programme (4Ps)

Case study

Harrow Case Study

Pounds £

CIS

Management

2003-11-28 00:00:00

Michael has this doc

IST Team

FALSE

Public Private Partnerships Programme (4Ps)

Case study

Kirklees Waste Case Study

Pounds £

CIS

Management

2003-11-28 00:00:00

Michael has this doc

IST Team

FALSE

Was this reply helpful? Yes No

Pstork1 69,125 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

First, It looks like there are some problems with the export. Order number and Archive show up as dates in everything except the first row. So I would double check the export to make sure the data is clean. That might clean up some of the missing dates too since they all seem to be in the second and higher row.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Verified answer

KS-20021558-0 41 on at

Like (0)

Report
Copy link

Link copied!
Hi P @Pstork1 ,
Thanks for calling that out — you’re right, the export had mixed types. Excel inferred Order number and Archive as dates for rows 2+ because the first data row looked like text while later rows looked numeric/date-like, so the whole column flipped to a date type and downstream fields went wonky (including the missing dates you saw). I’ve fixed this at source and in the flow: (1) In Access, I enforced field types in the export query: Order number = Short Text, Archive = Yes/No (or Short Text), and real Date/Time for the actual date fields; then exported to XLSX with “Export data with formatting and layout” unchecked to avoid Excel’s auto-formatting. (2) In Excel, I loaded via Power Query, set column types explicitly (dates using Using Locale… → Date → English (United Kingdom)), and loaded back to the same Table so every row shares the same schema. (3) In Power Automate, I set List rows → DateTime format = ISO 8601 and, in Create item, I normalise the date and send null for blanks to stop the String/date-time error:

if( empty(trim(string(item()?['<DateColumn>']))), null, if( contains(string(item()?['<DateColumn>']), '-'), first(split(string(item()?['<DateColumn>']),'T')), if( contains(string(item()?['<DateColumn>']), '/'), concat(split(string(item()?['<DateColumn>']),'/')[2],'-',padLeft(split(string(item()?['<DateColumn>']),'/')[1],2,'0'),'-',padLeft(split(string(item()?['<DateColumn>']),'/')[0],2,'0')), formatDateTime(addDays('1899-12-30', int(item()?['<DateColumn>'])), 'yyyy-MM-dd') ) ) )

After enforcing the export types and using the expression above, Order number and Archive no longer auto-convert to dates, and the SharePoint Create item step accepts either a valid ISO date or null without errors.

Was this reply helpful? Yes No