cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lowdmt
Regular Visitor

Uploading excel file to SharePoint list where cells contain a space

I have an excel file generated by a system I have no control over. I want to upload the contents of the file to a SharePoint list. The excel file has some date columns so I'm using the expression:

 

 

 

if(empty(item()?['Date booked for Delivery']),null,addDays('1899-12-30',int(item()?['Date booked for Delivery']),'yyyy-MM-dd'))

 

 

 

where 'Date booked for Delivery' is a column with dates. Not all cells in the column have a date in them (hence the if statement)

 

The flow had several failures and it took me a little while to figure out the blank cells failed....they aren't blank...they have a SPACE in them! 

 

I can't change the source file. I don't know how to have powerautomate manage this. Have a flow to remove the space first? Perhaps ignore cells that are blank or contain a space?

 

Certainly noob at this. Help appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
eric-cheng
Solution Sage
Solution Sage

HI @lowdmt ,

 

Are you using the List rows present action in your flow?  If so, you can actually set the DateTime format to ISO 8601 so you don't need to use addDays() and simply have formatDateTime()

 

ericcheng_0-1633685636546.png

 

To answer your other question, you can use a combination of equals() and or() in your expression, see below:

 

if(or(empty(item()?['Date booked for Delivery']),equals(item()?['Date booked for Delivery'], ' ')),null,addDays('1899-12-30',int(item()?['Date booked for Delivery']),'yyyy-MM-dd'))

 

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

View solution in original post

2 REPLIES 2
eric-cheng
Solution Sage
Solution Sage

HI @lowdmt ,

 

Are you using the List rows present action in your flow?  If so, you can actually set the DateTime format to ISO 8601 so you don't need to use addDays() and simply have formatDateTime()

 

ericcheng_0-1633685636546.png

 

To answer your other question, you can use a combination of equals() and or() in your expression, see below:

 

if(or(empty(item()?['Date booked for Delivery']),equals(item()?['Date booked for Delivery'], ' ')),null,addDays('1899-12-30',int(item()?['Date booked for Delivery']),'yyyy-MM-dd'))

 

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

View solution in original post

lowdmt
Regular Visitor

@eric-cheng - thank you. I used the OR to accomplish what I needed. Works fine.

 

I didn't look at the ISO8601 option - something I need to get time to look at. 

 

Thank you

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,561)