[HELP] Updating Sharepoint List from Excel

[EAGLE2023]

Hi All,

(Apologies if this has already been answered, I have trawled through the community and all similar issues and solutions haven't worked)

I have a set of audit data in excel, which is updated every time there is a new audit, (and a corresponding Sharepoint List).

Date Audited	Site ID	Service Type	Region	County
28/06/22	B00000001	A	Yorkshire & The Humber	South Yorkshire
17/09/22	B00000002	B	North West	Lancashire
16/08/22	B00000003	B	North West	Lancashire
24/03/22	B00000004	A	North West	Lancashire
16/08/22	B00000005	B	North West	Lancashire
01/09/22	B00000006	A	North West	Lancashire
18/08/22	B00000007	A	North West	Lancashire
29/06/22	B00000008	B	Yorkshire & The Humber	South Yorkshire
08/06/21	B00000007	A	North West	Lancashire
30/08/22	B00000002	B	North West	Lancashire
08/07/21	B00000001	A	Yorkshire & The Humber	South Yorkshire

My goal is simple. I want to update the SP List with a new row where the Site ID and Date Audited do not match, as sites are audited multiple times. However, I cannot get it to work no matter what way I try, getting different errors each time.

This is my current flow:123

This is the error:

Unfortunately, this isn't the only task I am having difficulty with, but any help is greatly appreciated!

[v-bofeng-msft]

Hi @EAGLE2023 ,

Please this action's(list rows) output in run history, the value of Date Audited seems not in datetime(ISO8601) format.

Best Regards,

Bof

[EAGLE2023]

Thanks for your reply @v-bofeng-msft. Here is the output error of list rows. I also added a compose action to check if the date was in ISO and seems like it is?

[v-bofeng-msft]

Hi @EAGLE2023 ,

Get it. 

You need to convert the string to datetime value. Please use 

parseDateTime(items('Apply_to_each')?['Date Audited'], 'en-GB', 'dd/MM/yy')

instead

I've made a test for your reference:

1\My excel table

2\Flow

3\Result:

Best Regards,
Bof

[AlexMCC]

The date format is incorrect.

Use ParseDateTime to format it correctly and you should be good.

[EAGLE2023]

Thanks @v-bofeng-msft and @AlexMCC. I have just set it to run, will update you if it fails/accept your solution if it succeeds.

[EAGLE2023]

Hi both @v-bofeng-msft @AlexMCC 
Had no better luck unfortunately. This is the error/output.

[AlexMCC]

Can you go to "Next failed" and screenshot the failed action?

What is your complete ParseDateTime formula?

[EAGLE2023]

This is the error; 

InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'In function 'parseDateTime', the value provided for date time string '44570' was not valid. The datetime string must match ISO 8601 format.'.

[AlexMCC]

Yes, but in your screenshot it seems the visible action did not fail, hence I wanted to see the result of the Next Failed since something must be different. 🙂

It could be that you have a trailing space or line break in one of your dates. So which one failed?

[EAGLE2023]

Apologies, I understand. This is the one that failed 

[AlexMCC]

It seems row 6 of your input has a different format than the previous rows, making it fail the ParseDateTime formula.

To isolate the issue, you could try composing just the original date, to see how that one row differs.

It seems like that row is actually using the Excel serial date format, meaning that one cell is formatted as "Date" in Excel, while the others aren't.

Fix your input data or write additional logic to accomodate for both scenarios, whichever suits the situation best.

A simple check could be to filter on date containing 8 (dd/MM/yy) or 5 (Excel serial) digits.

[EAGLE2023]

My initial thought was to ensure all the cells in that column were formatted as 'Date'. How would I write the filter array expression for containing dd/MM/yy? Apologies, I am new to PA so having to learn as I go.

[AlexMCC]

---

@EAGLE2023 wrote:

How would I write the filter array expression for containing dd/MM/yy?

Sorry, I'm not sure what you mean by this?

[EAGLE2023]

@AlexMCC You said filter on date containing 8 (dd/MM/yy), how would I do that? I don't know if I understood what you meant initially, my apologies.

[AlexMCC]

You can use an if() statement in your formula to account for two different date formats.

However, I'm struggling to understand why one cell would give you the serial date, since you have DateTime Format set to ISO 8601 in your very first screen shot. There's no chance you changed this setting?

Honestly, I think the best approach is to ensure good data is fed to the flow, opposed to trying to accomodate for bad data. If you have two different date formats today, who says you can't suddenly have three?

Look into how the data is written and, if it's within your control, make sure it's consistent.

[EAGLE2023]

I am 110% sure. This was precisely the reason I created this topic as I wouldn't need the compose action when the DateTime Format is set to ISO 8601 in my 'List rows present in a table' action?

The only potential issue I have seen in the data entered into excel is for dates such as '01/09/22', show as '1/9/22' in the cell. This only shows in the excel online workbook, not the workbook on my desktop, if that makes sense? 
Could that be the issue? 

[EAGLE2023 · Accepted solution]

SOLVED!

I ended up using 'formatDateTime' rather than 'parseDateTime', and it worked! 
Also the 'locale' was positioned in the incorrect place. Previously it = 

parseDateTime(items('Apply_to_each_3')?['Date Audited'], 'en-GB','dd/MM/yy')

When it should have been;

formatDateTime(items('Apply_to_each_3')?['Date Audited'], 'dd/MM/yy', 'en-GB')

However, thank you for all your help with this I wouldn't have been able to have solved it without you! 🙂