Unanswered

Setting and Using Variables for adding and updating List from external Excel data

(0) Share

Report

Posted on by Community Power Pla...

Hi,

I was able to successfully build a scheduled flow to update/add text and $ fields from a read-only Excel file to a SharePoint List. I have not been successful in getting 4 date fields (for 1 record) populated into the List. Here are the details I'm working with and hope someone can provide some help:

In some cases, the date fields have not yet be populated so they are blank and I wish for them to remain blank and only updated in my List as applicable during schedule runs.
Two of the dates fields are Date and Time: "Last Approval Date": "43684.6311919329" and "Submitted Date": "43340.5425716088"
The other two dates are just Dates only: "Start Date": "42826" and "End Date": "43951"
I Initiated Variables for each date at the very beginning as I remember from my coding days and have tried both setting them as Integer and Float types.

Of the several things I have tried and seen I'm now just lost and confused and have so many questions.

What Type should I be using for the initial variable? I initially used Float for the fields that had Date and Time, and Integer if it was Date only.
Do I need to use Set Variables or Compose with the formulas? Here are the formulas I captured.

Date only formula:	addDays('1899-12-30',int(item()?['YourDate']),'mm/dd/yyyy')
Date and Time formula:	addSeconds('1899-12-30',int(formatNumber(mul(variables('SerialDateTime'),86400),'0','en-us')),'mm/dd/yyyy HH:mm:ss')

*YourDate and SerialDateTime should reflect the Excel field name?

3. At what level in my flow am I supposed to put the Variable set and/or compose with formula to see success in the output to be ready to be updated in my SharePoint List? I got errors about 'valid arrays', 'not a string type', and others during my varied combination attempts.

4. How do I account for if the date field in Excel is blank without it giving me errors?

Here is an overview of my flow. All date fields have been left blank to get at least some data I can work with, but of course the full efficiency that can be realized by PowerAutomate is yet to be realized until I can get the Date field issues resolved.

Thanks in advance!

Spoiler (Highlight to read)

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

danvarga Microsoft Employee on at

Like (1)

Report

Hi @Anonymous could you please provide more information about the first variables values? I want to understand the context to answer the questions.

1. Please provide the information about the values or the context about the values, I'm not understanding what do you want with the variables because I understood that you have all columns in excel file and then copy the records into Sharepoint.
2. Yes, would be better with the formulas.
3. Depends of what you want in the beginning the variables should be recognized later. But I don't have all the context.
4. I need to test but I think that you can do it validating if the field is null or blank with the conditional component and then decide to set the value with a default or blank.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@danvarga Thanks for replying... Apparently there are some discrepancies in the data that is making it over. SIGH. I'm getting duplicated items in my SharePoint List. So having gone back to square 1 I'm beginning to think that it may be enterprise environment/accessibility settings at the root. The Excel file is in a SharePoint site Library as read only, and my List that I need will be adding additional fields to suit my teams needs is on another SharePoint site List. 🤔

I will definitely let you know how it all plays out.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

@danvarga , A co-worker figured out how to get dates from Excel into my SharePoint List successfully by initializing the variable as a string, and then using the below variable formula:
if(empty(item()?['Start Date']),null,addDays('12-30-1899',int(item()?['Start Date']),'MM-dd-yyyy'))

The issue still remains with the Excel Date values that include date and time. I am getting the error:
"The variable 'LastApproval' of type 'Float' cannot be set to null or empty value."
Any idea what I am missing to get the date and time field over via Automate? Thanks

Was this reply helpful? Yes No