Convert text from Excel table to timestamp for SharePoint

[ThomasEdvardsen]

Hi, I'm trying to convert the text from Excel "25.03.2020" to the date field in SharePoint. How can I get it done?

[efialttes]

Hi!

My suggestion is to manipulate the string by means of WDL functions

Which excel action block are you using? List rows from excel table?

Give me some minutes to work with it

[ThomasEdvardsen]

@efialttes  Here si a picture og my Flow. I am not familiar with WDL?

[efialttes]

Hi again

So I was assuming you will add a Select action block, assign 'List rows present in a table' output  its input

If so, one of your rules in the mapping table could be as follows:

Name: Sludatto

Value: assign the following expression

concat(split(item()?['Slutdatto'],'.')[2],'-',split(item()?['Slutdatto'],'.')[1],'-',split(item()?['Slutdatto'],'.')[0],'T00:00:00.0000000Z')

But since you are using an 'Apply to each', the expression is valid also

Hope this helps

[ThomasEdvardsen · Accepted solution]

@efialttes  I tried this formula with it did not work.

concat(split(@{items('Apply_to_each')?['Start dato']},'.')[2],'-',split(@{items('Apply_to_each')?['Start dato']},'.')[1],'-',split(@{items('Apply_to_each')?['Start dato']},'.')[0],'T00:00:00.0000000Z')

Is there a better solution for splitting text into a date that the SharPoint list understands?

[efialttes]

Hi
Can you share a screenshot from your flow design to see how and where you tried to add the expression?
Please also try removing space from 'Start dato' from your expression. Use 'Startdato' instead
Thanx!

[ThomasEdvardsen]

[efialttes]

You almost got it. Please try removing space from 'Start dato' from your expression. Use 'Startdato' instead

[ThomasEdvardsen]

Sorry, forgot to mention that I couldn't save the formula

[efialttes]

Hi again

Let's try with

concat(split(items('Apply_to_each')?['Startdato'],'.')[2],'-',split(items('Apply_to_each')?['Startdato'],'.')[1],'-',split(items('Apply_to_each')?['Startdato'],'.')[0],'T00:00:00.0000000Z')

Give me some minutes to test it

Thanx!

[efialttes]

Hi

Flow editor accepts my last expression suggested, I just added it in into a Compose since it is faster to validate it this way

Sometimes you need to click on 'OK' twice

Hope this helps

[ThomasEdvardsen]

[efialttes]

Hi!

So editor finally accepted the expression, right?

We are closer to a happy ending, then!

The new error comes from the fact the source field -Startdato, right?- is sometimes empty (null) so in our current flow execution it does not have a date stored inside.

Shall we use a default date in such cases? Shall we skip 'Opprett element 2' execution? Shall we...?

Please confirm requirements for such scenario

Thanx!

[ThomasEdvardsen]

yes we can skip if it is empty

[ThomasEdvardsen]

@efialttes 

I can use text fields too, but then I need to be able to turn the text from 25.03.2020 to 2020.03.25 or 20200325. In fact, it can solve the problem better for me as I do not have to convert it to text later.

Maybe you have a simple cover letter to turn around text?

[efialttes]

---

@ThomasEdvardsen wrote:

yes we can skip if it is empty

---

So, in such case my suggestion is to add a new evaluation rule to your Condition. Let's see if this one makes the magic

Left side: assign 'Start dato' dynamic content

Right side: assign the following expression:

null

Operator: not equals

Link both evaluation rules (the one already defined, with new one) by means of AND operator

So hopefully when there is no value assigned to 'Start data'... condition fails and flow execution takes the false branch

If you need further assistance, please let me know

Thanx! 

[ThomasEdvardsen]

I made it by creating variables.
Since all the rows have the same date, I can do that.

Thank you verry much for helping me @efialttes 

Solution:

concat(split(body('Get_a_row')?['Start dato'],'.')[2],'-',split(body('Get_a_row')?['Start dato'],'.')[1],'-',split(body('Get_a_row')?['Start dato'],'.')[0],'T00:00:00.0000000Z')

 

[efialttes · Accepted solution]

HURRAY!

IT WORKS!

My humble suggestion: mark this topic as "Solved" by clicking "Accept as a solution". This way other community members facing this same problem in the future have the chance to find a solution faster. Please note you can even select several replies as as 'Solution', even from different community members!

Thanx for your contribution to keep this community great!

[Anonymous]

Can you tell me where to learn what means @ { etc? I want to build better expressions however I don't know on which programming language is that based on or where is documentation.

[efialttes]

---

@Anonymous wrote:

Can you tell me where to learn what means @ { etc? I want to build better expressions however I don't know on which programming language is that based on or where is documentation.

---

Expressions are based on functions from Workflow Definition Language

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference

Hope this helps