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

Importing Excel data to existing SharePoint list fails with blank email cells

How can I get blank email cells in Excel to not error out when importing into an existing SharePoint list?

A flow I found online to import Excel data into an existing SharePoint list fails when an email address field is blank in Excel. The flow works fine without blanks; it also works if I manually paste in multiple rows. But I have thousands of them to enter.

To try to manage the blanks, I set a string variable for the email. In the Create Item portion, I added the following expression, but when I run the flow, NO email addresses end up in SharePoint, but I get no errors, either:

 

if(equals(items('Apply_to_each')?['strEmailAddress'],''),null,item()?['strEmailAddress']?['Value'])

 

The following, similar, expression also doesn't work:

 

if(empty(item()?['strEmailAddress']),null,item()?['strEmailAddress'])

 

Looking at the inputs, I see that null is in all of the items.

For reference, here's the flow - the email is "Employee3":

HansPNW_1-1618985215240.png

HansPNW_2-1618985374078.png

 

So, next I tried an alternate method, whereby instead of using an expression for the email variable in the Create Item portion, I used a condition to test if an email cell was blank, and if so, set the strEmailAddress variable to null; however, setting the Value to null doesn't work, nor does adding null or 'null' to an expression. Here it is:

HansPNW_3-1618985531322.png

HansPNW_4-1618985625814.png

 

How can I get the flow to work when there is no email address?

5 REPLIES 5
CFernandes
Super User
Super User

Hey @HansPNW 

 

Can you try the expression below -

 

if(equals(<your item>,''),null,<your item>)

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Hi @CFernandes - thanks for your reply.

I tried the expression, but PA says it's invalid.

I just found the following solution, which seems overly complicated for what I want to do:

Solved: Enter null/blank value into a Date column of a Sha... - Power Platform Community (microsoft....

I'd prefer to just use an expression.

@HansPNW Can paste you expression in here..

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

@CFernandes-

Here it is:

if(equals(strEmailAddress,''),null,strEmailAddress)

Hey Mate,

 

If you add a compose and look up the item does it work -

 

item()?['strEmailAddress']

 

If yes, the formula should be - 

 

if(equals(item()?['strEmailAddress'],''),null,item()?['strEmailAddress'])

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (5,246)