cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How can I output a range of split lines Power Automate

I followed this link to learn how to split my csv file into multiple lines. https://powerusers.microsoft.com/t5/Building-Flows/Sliting-multiple-lines/td-p/90297

It only shows how to export 1 line with expression " outputs('Compose_Find_NewLines')[0] "

Is is possible to output a range of lines? I am trying to split the csv file into multiple files of certain ranges.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

@Mike_BE 

THat's amazing! The first time I see Power Automate dealing with such array size! Thanx for sharing!

 

Now, as promised the screenshots from my test design. Please note my input array has just 41 elements, and I am splitting it in groups of 8, so you need to adapt it to your 80,000 scenario. I have successfully executed it, unclear to me the performance when handling 80,000 slices.

So, remember, when I use number 8 in my expressions, you should replace it and use 80000 instead

Flow_slice400Karray_1.png

Flow_slice400Karray_2.png

So, once Do Until completes its work, the remaining elements will be stored in variable 'auxArray', you just need to store them in a new file.

 

Please don't forget to share your progress on this challenge!

And... thanx for your kindness!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

12 REPLIES 12
Highlighted
Dual Super User III
Dual Super User III

Hi!

From your description it seems

 

 

outputs('Compose_Find_NewLines')

 

 

is an array with a variable number of elements, each element one row from your CSV file

 

So, the expression

 

 

outputs('Compose_Find_NewLines')[0]

 

 

represents the first row.

First CSV row is also represented by this other expression

 

first(outputs('Compose_Find_NewLines'))

 

MEaning last row is represented by this expression

 

last(outputs('Compose_Find_NewLines'))

 

 

 

Since you have already splitted your CSV file into rows,you can iterate through your array elements (i.e. CSV rows) by means of an 'Apply to each', a 'Filter array' or even a 'Select' action block.

So the question is, what do you need to do next?

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

Hello. Thanks for responding.

I get a tab delimited file and convert it into CSV with power automate. The file I get is between 380k and 390k lines. I would like to split it into 5 files with none of them exceeding 80k lines. Right now I download it and split in excel manually 80k at a time and just have the left over in the last file (60k ish). I don't know exactly how many lines the initial file will be so the last file always changes sizes. Could you help me figure this out?

Edit:

Better explanation.
I would like to split a large 380k file into 5 files by lines.

File 1 = Lines 1 - 80,000

File 2 = Lines 80,001 - 160,000

File 3 = Lines 160,001 - 240,000

File 4 = Lines 240,001 - 320,000

File 5 = Lines 320,000 - Last

Highlighted
Dual Super User III
Dual Super User III

Hi!
"I get a tab delimited file and convert it into CSV with power automate"
Does it mean you already habe this part up and running?
Thanx!


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

@Mike_BE 

Your solution will likely involve an apply to each action.  Unless you have a premium license of some type, you won't be able to loop more than 5,000 times - that's the limit.

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Highlighted

I do not have a premium license. Would you still be able to show me how to do this so if I do decide to get one I can easily apply it?

Highlighted

@ScottShearer @Mike_BE 

Do we really need to use 'Apply to each'? I don't think so

 

My first concern is... Can Power Automate read a CSV with more than 400,000 rows and store them in an array?

 

If the answer this question is yes, I think we can implement a solution without using 'Apply to each'.

And if I am wrong, nested 'Apply to Each' can theoretically be a valid solution also -but extremely time consuming-, as explained here:

https://powerusers.microsoft.com/t5/Building-Flows/Data-Collection-Inside-Flow/m-p/508124#M64120

 

Let's try to figure out how you can implement the algorithm without 'Apply to each':

So I am assuming you at least have a flow that reads the csv file as explained in your first post right, and that you already test it with a 400,000 rows length csv file.

Now, once you get the huge array stored in

 

outputs('Compose_Find_NewLines')

 

we can calculate nr of elements. Let's store this value in a variable: add an Initialize variable, type integer, let's call it Lenght, assign the following expression as its value:

 

length(outputs('Compose_Find_NewLines'))

 

 

 

we will probably need counter. Let's store this value in a variable: add an Initialize variable, type integer, let's call it i, assign 0 as value.

We can calculate the nr of full 80,000 pieces you should split it. We will add a Do Until loop and will evaluate if our counter i already reached this value by means of the following expression:

 

div(variables('Length')),80000) 

 

 

What should we do inside the Do Until?

-extract first 80,000 elements from the input array, and store it in a file. take() function should do the magic

-remove first 80,000 elements from the input array, so you will probably need an auxiliar array. skip() function should do the magic

-increment counter

 

Once outside the Do Until all we need to do is to store the input array remaining elements in another file (the 5th one)

 

@Mike_BE If you finally opt to implement this algorithm, please let us know your progress

 

Hope this helps

 

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

This is my current flow step by step. I am fairly new to this so i'm having a bit of trouble understanding yours steps when it gets to "do until".


image.png

This grabs the file from the SFTP server. Adds a time stamp to file name and saves to a folder on my onedrive so I have the original file.

 

 


 image.png

Turns the file content into a string

 

 

image.png

Makes tab variable TabChar

 

 

 

image.png

replace(variables('File'),variables('TabChar'),',')
 
 
image.png
decodeUriComponent('%0A')
 
 
image.png

outputs Compose NewLine

 

 

image.png

split(outputs('Compose_Text'), outputs('Compose_NewLine'))
 
 
image.png
length(outputs('Compose_Find_NewLines'))
 
image.png
 
 
Thats as far as i've gotten. I also checked to make sure power automate is reading the arrays correctly by using outputs('Compose_Find_NewLines')[0] and changing the last number to other ones. 
[250000] and [383054]. I then checked the original sheet and it lined up with the output
 
Highlighted

image.png

 

I added this do until with a 10(?) loop.

Yes = outputs('Compose_Find_NewLines')[0]

no = outputs('Compose_Find_NewLines')['i']

Then incremented 'i' by 1. 
After waiting 10 mins it was still processing the do until. Do not think I did it correctly.

Highlighted
Dual Super User III
Dual Super User III

Hi!

Can you execute your new flow as is, inspect content stored in variable 'length' and share it?

In the meantime I will work on the Do Until part to share a screenshot.

 

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

image.png

 

Here is the image of the length. It is showing the correct amount

Highlighted
Dual Super User III
Dual Super User III

@Mike_BE 

THat's amazing! The first time I see Power Automate dealing with such array size! Thanx for sharing!

 

Now, as promised the screenshots from my test design. Please note my input array has just 41 elements, and I am splitting it in groups of 8, so you need to adapt it to your 80,000 scenario. I have successfully executed it, unclear to me the performance when handling 80,000 slices.

So, remember, when I use number 8 in my expressions, you should replace it and use 80000 instead

Flow_slice400Karray_1.png

Flow_slice400Karray_2.png

So, once Do Until completes its work, the remaining elements will be stored in variable 'auxArray', you just need to store them in a new file.

 

Please don't forget to share your progress on this challenge!

And... thanx for your kindness!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted

It worked!!

Kind of. It would work better for smaller splits. Because of the amount of lines the do until is extremely slow. Took about 1 1/2 hours for it to fully run. Once I split it and did it 1 step at a time with the create files at the end it took about 2-3 mins to run! I also ended up switching the files back to a txt tab delimited since it will also work and seemed to speed it up more. Thank you for all your help!!!

 

 image.png

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (9,064)