cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevinsa
Helper I
Helper I

Text to CSV - Grouped Rows

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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":

 

DamoBird365_0-1641400294194.png

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:

DamoBird365_1-1641400343995.png

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

 

DamoBird365_2-1641400889946.png

 

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:
DamoBird365_3-1641401459669.png

  

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

trim(substring(body('Filter_array')?[add(mul(4,item()),1)],46))
 
DamoBird365_4-1641401523301.png

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

 
 

 

 

View solution in original post

3 REPLIES 3
DamoBird365
Super User
Super User

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

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":

 

DamoBird365_0-1641400294194.png

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:

DamoBird365_1-1641400343995.png

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

 

DamoBird365_2-1641400889946.png

 

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:
DamoBird365_3-1641401459669.png

  

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

trim(substring(body('Filter_array')?[add(mul(4,item()),1)],46))
 
DamoBird365_4-1641401523301.png

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
Super User
Super User

{"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:

 

DamoBird365_0-1641401917252.png

 

Damien

 

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (1,213)