Keep certain columns from a CSV

[dBrand]

I have a flow that takes an array that is passed in and I used the Create CSV table action to make it a CSV table. I then want to keep certain columns that is passed in as a list of fields (like: Name,Phone,Age). The columns to keep are not predefined and is dynamic when the flow is run based on the info passed in specifying the columns to keep. Is there a way to do this?

[ManishSolanki]

Hi @dBrand 

Pls set "Columns" parameter as "Custom". Enter the text for header and add expression for value as shown below:

item()?['Name']

Add the other header value with the similar pattern: item()?['columnName']

Here, columnName will be the name of input columns passed in "From" parameter.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[Nived_Nambiar]

Hi @ManishSolanki 

I think this approach will only work when number of columns to be considered are fixed irrespective of column names.

@dBrand  - it seems like columns required will be dynamically supplied at the start of flow itself.

If yes - then follow the below approach 

1. I am using manually trigger flow approach - here i will provide the input as column names seperated by comma at start of flow like below

2. I am storing the csv file in a sharepoint folder. so i am using get file content to get file content and subsequent compose action operation to get the file content using below expression.

 

Expression:  trim(base64ToString(outputs('Get_file_content')?['body']?['$content']))

3. Initialize headers variable which stores headers from csv file using below expression 

Expression:  split(trim(split(outputs('Compose-CSVTable'),decodeUriComponent('%0A'))?[0]),',')

4. Initialize a FinalTableCSV which stores final csv table with columns required.

5. Initialize a object variable with name tempobj like below

6. The below 2 variables are initialized to find the position/index positions of headers

7. Now use apply to each 3 loop to loop through headers variable to get the index position of each header in csv file 

•  Use compose action to add header as property name and index position of it as value like below
  
  Expression: addProperty(variables('headerpos'),items('Apply_to_each_3'),variables('index'))
• Use set variable to set the output of compose action to headerpos variable
  
• Increment the index variable by 1

This loop's output would be json object which has index position of all headers present in csv table.

8. Use another apply to each loop to loop the csv contents like below

Expression used:  skip(split(outputs('Compose-CSVTable'),decodeUriComponent('%0A')),1)

Next steps will be done inside the loop (apply to each loop)

8.1 Use another loop - Apply to each 2 loop inside the Apply to each loop like below

8.2 Expression:  split(triggerBody()['text'],',')

              Inside the Apply to Each 2 loop , do the below sub-steps -

             8.2.1 use compose action to add a property with name of header and its corresponding value to tempobj object 

         Expression:  addProperty(variables('tempobj'),items('Apply_to_each_2'),split(items('Apply_to_each'),',')?[variables('headerpos')?[items('Apply_to_each_2')]])

         8.2.2 Use set variable to set the output of above compose action to tempobj variable

Outside the apply to each 2 loop but within the Apply to each do the below steps 

8.3- append the tempobj to the FinalCSVTable variable

8.4- set tempobj as {} to reset the value before starting of next iteration.

Outside the loop (Apply to each) - do the following

9- use create csv table to create csv table out of FinalCSVTable 

Let's see result in action 

Let's assume this is our csv file 

Now we want columns - A,B,C out of it . 

While running the flow , we specify the columns needed using manually trigger a flow input 

Now see the result at the end :-

Hope this flow helps ! 

Let me know if you need any help !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[Nived_Nambiar · Accepted solution]

Hi @ManishSolanki 

I think this approach will only work when number of columns to be considered are fixed irrespective of column names.

@dBrand  - it seems like columns required will be dynamically supplied at the start of flow itself.

If yes - then follow the below approach 

1. I am using manually trigger flow approach - here i will provide the input as column names seperated by comma at start of flow like below

2. I am storing the csv file in a sharepoint folder. so i am using get file content to get file content and subsequent compose action operation to get the file content using below expression.

 

Expression:  trim(base64ToString(outputs('Get_file_content')?['body']?['$content']))

3. Initialize headers variable which stores headers from csv file using below expression 

Expression:  split(trim(split(outputs('Compose-CSVTable'),decodeUriComponent('%0A'))?[0]),',')

4. Initialize a FinalTableCSV which stores final csv table with columns required.

5. Initialize a object variable with name tempobj like below

6. The below 2 variables are initialized to find the position/index positions of headers

7. Now use apply to each 3 loop to loop through headers variable to get the index position of each header in csv file 

•  Use compose action to add header as property name and index position of it as value like below
  
  Expression: addProperty(variables('headerpos'),items('Apply_to_each_3'),variables('index'))
• Use set variable to set the output of compose action to headerpos variable
  
• Increment the index variable by 1

This loop's output would be json object which has index position of all headers present in csv table.

8. Use another apply to each loop to loop the csv contents like below

Expression used:  skip(split(outputs('Compose-CSVTable'),decodeUriComponent('%0A')),1)

Next steps will be done inside the loop (apply to each loop)

8.1 Use another loop - Apply to each 2 loop inside the Apply to each loop like below

8.2 Expression:  split(triggerBody()['text'],',')

              Inside the Apply to Each 2 loop , do the below sub-steps -

             8.2.1 use compose action to add a property with name of header and its corresponding value to tempobj object 

         Expression:  addProperty(variables('tempobj'),items('Apply_to_each_2'),split(items('Apply_to_each'),',')?[variables('headerpos')?[items('Apply_to_each_2')]])

         8.2.2 Use set variable to set the output of above compose action to tempobj variable

Outside the apply to each 2 loop but within the Apply to each do the below steps 

8.3- append the tempobj to the FinalCSVTable variable

8.4- set tempobj as {} to reset the value before starting of next iteration.

Outside the loop (Apply to each) - do the following

9- use create csv table to create csv table out of FinalCSVTable 

Let's see result in action 

Let's assume this is our csv file 

Now we want columns - A,B,C out of it . 

While running the flow , we specify the columns needed using manually trigger a flow input 

Now see the result at the end :-

Hope this flow helps ! 

Let me know if you need any help !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[dBrand]

Thanks - stuck trying to determine where the input parameter for the columns to show is in the flow? I don't see it referenced. Am I missing something?

[Nived_Nambiar]

Hi @dBrand 

sorry i have forget to add that - just check my above response, I have added an input in manually trigger a flow which captures the columns to be considered.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[dBrand]

Thanks. I don’t see where the input ‘ColumnsInclude’ is used to narrow down the columns to return. Am I missing something? I appreciate all the help!

[dBrand]

Thanks, I appreciate the reply. I need this to work with dynamic number of and selection of columns, which can change every time. But will keep this aside. Thanks. 

[Nived_Nambiar]

Hi @dBrand 

The loop apply to each 2 does the looping through the required headers 

split(triggerBody()['text'],',')

Hope it helps 

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[dBrand]

Thanks. I'll test this out and give an update.

[dBrand]

Thanks! Your solution works as expected. Wish doing things like this wasn't so complicated. Thanks again!!!

[Nived_Nambiar]

Actually it shouldn't be too complicated. If we have json array structure of that csv data earlier, things would be quite easier than this. I thought to do like that but it would be difficult to re create the csv structure from scratch if we have many columns in csv.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌