Text to CSV - Grouped Rows

[kevinsa]

Previously, @DamoBird365 was able to provide excellent assistance in taking a text file and converting it into a CSV which I have been able to do some great work with. That post is available here:

Solved: Re: Flat File to CSV - Power Platform Community (microsoft.com)

What I'm running into now is doing the same thing, but this time the data I need to convert to a CSV is split over multiple lines. Below is a sample of what I have to work with and what I am trying to get as a CSV. 

Data I need to convert to a CSV:

GroupCD# Description                    DP/CT Description 
======== ============================== ===== ==============================
01022311 Paper                           01   Paper 
                                         02   Type 
         Comment                         23   Weight 
                                         11   Colour 

21032198 Buckets                         21   Buckets 
                                         03   Purpose 
         Comment                         21   Size 
                                         98   Colour 

Desired CSV output:

GroupCD#,DP,DP_Desc,Sub_DP,Sub_DP_Desc,CT,CT_Desc,Sub_CT,Sub_CT_Desc
01022311,01,Paper,02,Type,23,Weight,11,Colour
21032198,21,Buckets,03,Purpose,21,Size,98,Colour

Any help would be appreciated.

[DamoBird365]

Hi @kevinsa 

Pretty interesting challenge.  Is this data from an API or a file?  If it's a file, does it come with a spec with field lengths/positions?  If it's based on length/position, we could use substring() to get the data and trim() to remove spaces.  This could be combined with several other expressions like split() to create an array and possibly a select action to create a new array.

Your example data is very useful, but if you could tell me a bit more about the source, I would like to come up with a possible solution.

Damien

[DamoBird365 · Accepted solution]

Hi @kevinsa 

I noticed your link to the other post, this is going to be a file - long day, sorry!

So, here I create an array of 4 lines for each "record":

Return Compose is a compose with a single return key press.  Filter array removes the two header rows with skip and the blank line with the item() is not equal to (Nothing).

Output of filter array is:

Then use a select action and a create CSV Table action:

The select From is based on an expression:

range(0,div(length(body('Filter_array')),4))

which in your example is 0->2, range runs to n-1, i.e. 0 - 1.

The Group CD# is based on an expression:

substring(body('Filter_array')?[mul(4,item())],0,8)

which if you break it down is substring from position 0->8 based on the string from

"01022311 Paper                           01   Paper "

which is position 0 of the filter array.  I use multiply by 4 (as there are 4 rows in each record).  4*0=0, 4*1=4 and so on.  This gets the object for the array at position 0,4,8,12 etc.

To get field data from the next object add+1

"                                         02   Type ",

Sub_DP: substring(body('Filter_array')?[add(mul(4,item()),1)],41,2)

Sub_DP_Desc: substring(body('Filter_array')?[add(mul(4,item()),1)],46)

**didn't bother with length in substring as it's an end of line

Here's an example output from the Create CSV Table:

to remove the trailing space from Type/Purpose, use trim().

trim(substring(body('Filter_array')?[add(mul(4,item()),1)],46))

Hope this helps and you are able to construct the other expressions.

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

[DamoBird365]

{"id":"3048f157-8dd2-445c-8bf9-7eed-846dab73","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"DamoBird365_Demo","operationDefinition":{"type":"Scope","actions":{"File":{"type":"Compose","inputs":"GroupCD# Description                    DP/CT Description \n======== ============================== ===== ==============================\n01022311 Paper                           01   Paper \n                                         02   Type \n         Comment                         23   Weight \n                                         11   Colour \n\n21032198 Buckets                         21   Buckets \n                                         03   Purpose \n         Comment                         21   Size \n                                         98   Colour ","runAfter":{}},"ReturnLine":{"type":"Compose","inputs":"\n","runAfter":{"File":["Succeeded"]}},"NewArray":{"type":"Compose","inputs":"@split(outputs('File'),outputs('ReturnLine'))","runAfter":{"ReturnLine":["Succeeded"]},"description":"split(outputs('File'),outputs('ReturnLine'))"},"Filter_array":{"type":"Query","inputs":{"from":"@\r\nskip(outputs('NewArray'),2)","where":"@not(equals(item(), ''))"},"runAfter":{"NewArray":["Succeeded"]},"description":"skip(outputs('NewArray'),2)"},"Select":{"type":"Select","inputs":{"from":"@range(0,div(length(body('Filter_array')),4))","select":{"GroupCD#":"@substring(body('Filter_array')?[mul(4,item())],0,8)","DP":"","DP_Desc":"","Sub_DP":"@substring(body('Filter_array')?[add(mul(4,item()),1)],41,2)","Sub_DP_Desc":"@trim(substring(body('Filter_array')?[add(mul(4,item()),1)],46))","CT":"","CT_Desc":"","Sub_CT":"","Sub_CT_Desc":""}},"runAfter":{"Filter_array":["Succeeded"]}},"Create_CSV_table":{"type":"Table","inputs":{"from":"@body('Select')","format":"CSV"},"runAfter":{"Select":["Succeeded"]}}},"runAfter":{}}}

If you want to copy the above code to your clipboard, create a new flow with a manual trigger, select + new step, navigate to my clipboard and hit ctrl+v, you can paste the solution into flow to try:

Damien