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.
Solved! Go to 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:
to remove the trailing space from Type/Purpose, use trim().
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
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":
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:
to remove the trailing space from Type/Purpose, use trim().
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
{"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
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
At the monthly call, connect with other leaders and find out how community makes your experience even better.
User | Count |
---|---|
26 | |
25 | |
23 | |
23 | |
20 |
User | Count |
---|---|
62 | |
42 | |
41 | |
29 | |
26 |