Excel to Sharepoint List (Excel Null or Error in Functions DIV etc)

(0) Share

Report

Posted on by ShaneITAutomate

301

Hi,

I have a simple Flow where a user in SharePoint selects a excel file from the SharePoint Document Library and clicks run, the flow runs and simply copies the table data from the selected Excel files table to a SharePoint List all works wonderful, However every so often the excel file has an null or DIV error such as the image below

How do i get the flow to simply enter any errors as 0 or empty?

Any help would be appreciated.

PS The excel files are sent to me externally so i have no control over this without manually checking and changing which will not suite

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

ccc333ab 1,144 on at

Like (0)

Report

You should be able to just check to see if the value being added to SharePoint is #DIV/0 and if so, either add in a null or don't even add anything in at all for that column (i.e., just skip it). Can you show a screenshot of your flow so I can see how you are adding items to SharePoint? Might be able to give a better answer than this if I can see what you are doing.

Was this reply helpful? Yes No
ShaneITAutomate 301 on at

Like (0)

Report

Im not quite sure how to do that using an excel file could you please do a quick example?, i could do it in Sharepoint but with excel and having to use the workaround to create dynamic table names etc how can i reference and change the #DIV/0 error?

Heres a pic of my flow as requested (all data is filled out and the flow works beautifully, however i removed the names etc for sensitive data security)

Was this reply helpful? Yes No
Verified answer

ccc333ab 1,144 on at

Like (1)

Report
Holy redacted screenshots 😄
Because I need to use something, I'm going to pretend there is a field in your list called "TotalAmount".

So I am going to guess that any of your fields you currently have your float expression could contain a #DIV/0, so then you would need to adjust all of those expressions to the following:

if(or(empty(items('Apply_to_each_2')?['TotalAmount']),equals(items('Apply_to_each_2')?['TotalAmount'],'#DIV/0!')),null,float(items('Apply_to_each_2')?['TotalAmount']))

What this expression does is to check if the Excel value is either "#DIV/0!" or it is empty. If so, add a NULL, otherwise convert it to a float and add that.

(I added the empty as I think you were saying that was possible in your initial post).

Was this reply helpful? Yes No
ShaneITAutomate 301 on at

Like (0)

Report

Thankyou, i was trying to complicate it with the update rows actions, a condition and filter queries lol. Cheers
Thanks

Was this reply helpful? Yes No