Split method - parse csv

[Kamil_M]

Hi,

In Teams when we create a task for students we can download lists of assign to this task users in CSV format.

This file looks like this:

I would like to create a flow which parse csv in loop and takes the e-mails . Then when we dont have the filled response i would like to send a notifications or create a task in outlook.
I created a simple flow to parse json and its works :

But i have a lot of troubles with parsing csv file and using split method to select specific rows and values 😕

If somebody will have a time to take a look at this i would be pleased.

[JohnAageAnderse]

Hello @Kamil_M 

Someone else had a similar issue as you have. Please try the solution as shown in this thread:

https://powerusers.microsoft.com/t5/Building-Flows/Sliting-multiple-lines/td-p/90297

Kind regards, John

[Kamil_M]

Thanks for Your quick answer 🙂

I knew that topic but isnt works for me. I think in my case is some kind of problem with line separator.

i used this function in compose 2 - split(variables('Lista'),take(first(skip(split(variables('Lista'),'  '),1)),1))

[JohnAageAnderse]

Hello @Kamil_M 

The first think I did after getting the file content of the csv file, was to see how it looked in a compose action. There I saw that for my system, the file contained "\r\n" after each line in the csv file. Yours may differ.

Anyway, based on this information, I added a compose action to create a string with a newline "\n".

The Compose CR string action contains two lines, the first is just a newline, the second is the "X".

The Extract the CR from the CR string uses an expression to split on the "X" giving an array of two values, which the expression then continues to take the first value from, thus the CR variable contains the newline. 

first(split(outputs('Compose_CR_string'),'X'))

The Split CSV into array of lines uses an expression to split the CSV file content by the CR variable value into separate lines into the CSVLines array variable.

split(body('Get_file_content_using_path'),variables('CR'))

Using a filter action, only the items in the CSVLines array variable that has an "@" character will be used. Had to use an expression with concat to be able to filter on that character.

concat('','@')

The output from the filter action is then iterated over.

Each line in the output is split by the comma ",' into columns.

split(item(),',')

Each email is then collected into the Email array by using an expression of skip and first, as the email address column position is known.

first(skip(variables('CSVColumns'),2))

You now have an array variable with all the email addresses found in the CSV file.

Note that the above does not contain all the actions - you need to initialize some of the variables used. 🙂

Kind regards, John

[v-litu-msft]

Hi @Kamil_M,

You could following my steps to move these line separator, then convert it into an array:

Use uriComponent() function to convert the string into URI code, then use replace() function to remove line separator("%0D%0A"), use ";" to instead it, then decode it, after that remove double quotes:

replace(decodeUriComponent(replace(uriComponent(body('Get_file_content_using_path')),'%0D%0A',';')),'"','')

Then use the split() function to create an array:

split(outputs('Compose'),';')

Then you can use the Apply to each action to loop through each item of the array, then create events. There is a post have more details about how to do it, you could have a reference to it:

https://powerusers.microsoft.com/t5/The-Power-Automate-Cookbook/read-csv-file-and-write-to-destinati...

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kamil_M · Accepted solution]

Hi,

Thanks to You i found solution., but i modified it for my own way 🙂

Instead of using conversion to URI and back again i just use:

uriComponentToString('%0A') as variable "empty".

I have to mention that solution like this below doesnt work :

It is a pity that, in the current situation of remote learning, tasks on Teams doesnt integrate with calendar.

Topic to close 🙂

[SahilJhamb]

Hi John! 

Thanks for the great workaround! I was trying the other solutions provided below but none was working with more than 500 rows. Your one worked with even 2000+ rows. Simply amazing!

[joelbicoypro]

Thank you @v-litu-msft !

this have worked for me! especially when the CSV file is taken from a url using HTTP connector having octet-stream returns.