Get data from .txt file that is in .csv format

[MadsV]

Hi Automaters,

I'm trying to create a flow where i get data from a .txt file located in Sharepoint. 

The format from the .txt file is: 

{"fletteFelt1":"01-11-2023-05-11-2023; 08-11-2023-12-11-2023; 14-11-2023-16-11-2023; 18-11-2023-","fletteFelt2":"19-11-2023;21-11-2023-26-11-2023; -; -","fletteFelt3":" -; -; -; -","fletteFelt4":" ; -; -; -","fletteFelt5":"-","fletteFelt6":" 16,00","fletteFelt7":"12345","fletteFelt8":"Test Person 1","fletteFelt9":"98765","fletteFelt10":"Test person 2","fletteFelt11":null,"fletteFelt12":null,"fletteFelt13":null,"fletteFelt14":null,"fletteFelt15":null,"fletteFelt16":null,"fletteFelt17":null,"fletteFelt18":null,"fletteFelt19":null,"fletteFelt20":null,"fletteFelt21":""}

I need to access the dates from "fletteFelt1" and "fletteFelt2" that has dates in it. 

I want to paste the data into Excel, where each date interval is their own row. 

E.g. 

1st Row: 01-11-2023-05-11-2023

2nd Row: 08-11-2023-12-11-2023

This should be doable since they are seperated by ; ?

Let me know if i need to elaborate further, i'm relatively new to automating.

Best regards,

Mads

[Rhiassuring]

Looking at the data above, I'd say you want to split on , first. You can use the split() function in Compose, through the Expressions.

You'll only work with 0 and 1 from that Array -- the first and the second items in the array. You can discard everything after that.

Then for those two sets, you'll first remove the first 14 characters from each, and the last 1 character. That'll get rid of the fletteFelt text from each so you have clean data.

Next, you'll split on ;. You will now have an Apply to Each nested in an Apply to Each. From there, you can just do "Add a Row" into your table with the "Current Row".

[MadsV]

Thank you so much @Rhiassuring 

I will give it a try, and accept your solution if it works! 😁

[Rhiassuring]

I'm about to go to bed (it's 2AM here) but I will be back tomorrow afternoon around 3PM Pacific, so if you get stuck, post what you're having issue with (and screenshots) and I can provide the rest of it.

[MadsV]

Quick question. This is how i would assume i should get the content, but it doesn't seem to work for me. 

When i try that simple flow it doesn't add anything to my excel sheet. Is the "Get filecontent" the wrong thing to use for getting my data 😀?

EDIT: (Added all here, so you just have to read one post) 

This is my first time working with the data operations, so I don't really have a clue how to handle the compose activity. 

Can you possibly create a "mock-up" and screenshot it, so i know what to put in where?

Your message makes a lot of sense, I just can't seem to put my thoughts into code. Which is very infuriating because i understand the logic behind it.

[Paulie78 · Accepted solution]

HI @MadsV ,

As you are a new user and some of the concepts may be unfamiliar to you, I made you a YouTube video that demonstrates what you are trying to do:

https://youtu.be/dO3NmvfWHmo

Let me know how you get on.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[MadsV]

@Paulie78 I love that you made a video! It really helps as a beginner being walked through the steps, and you do it very well!

If you have time I would love to progress further into this flow, because I would love to make it more advanced, but I simply lack the understanding (as of now).

SS of the data in Excel from the flow

SS of what i would like it to be (On the right side) 

So the data i take from the JSON file is date intervals, and i would like them written out as each date inbetween the interval. 
Also on the row with 18-11-2023- the interval is in the next row. (I assume because there wasn't more space in flettefelt1 so it was moved to flettefelt2)

Is it possible to ensure that the intervals will be coherent with a condition possibly?

I hope my questions make sense, please tell me if i need to elaborate, or explain myself better 😀

EDIT:
I have talked with the person who delivers the data and made som changes. So in the future i will only get date intervals in the same flettefelt, and the date intervals will be seperated by '/' instead of '-'. Which should make it easier to split the dateinterval?
So the question is how do i get each date inbetween the interval? 😅

[Paulie78]

Yes, it is certainly possible. It requires a bit more work than I have done in the flow in the video demo though.

[MadsV]

@Paulie78 

From what you taught me, I assume I have to split the array by using "/" as the delimiter. But I don't know how to get the two dates in their own variable. 

I want to have the start and the end date in to seperate variables. 

e.g

Start date: 01-11-2023 

End date: 05-11-2023

I've tried using the filtermatrix and use starts with / ends with "/" but this doesn't work because it's an array and not just a string.