How to create CSV file in Power Automate

Export to CSV is one of the common requirements in any application. But in this example, we are not going to show the procedure from the app that will be shared in a separate post. Here we are getting the data from Power Automate itself to support a large set of data.

Manual flow is used in this example

In this example, two approaches are explained:

1. Type 1 – Using “Create CSV” action (recommended)
2. Type 2 – Using “Apply to each” (the classic way)

Initialize an array variable and get items from the list.

Type 1 – Recommended, A simple straight forward action no customization is needed until you need to manipulate the data with expressions.

Type 2 – Create an “Apply to each” action and pass the “Value of items” from dynamic data. Create a compose action with a new line, it will be used in concatenation.

We have to append the string variable, with the values as “Comma Separated Values”(CSV) and at the end, the previous step output (New Line Compose Action) should be added. It will fetch the result but the output will not be in an expected format.

Because CSV files are delimited with commas if any column value has a comma in it will give unexpected results shown in the below screenshot.

// Output of Create CSV Action
S.No,Application,Description,LicenseDate
1,Canvas,"Provdies more option in UI, highly customizable",2021-11-26
2,Cloud Flow,Automates the process,2021-11-10
3,Model Driven,Component based approach,2021-11-03

//Output of Apply to each Action
1, Canvas ,Provdies more option in UI, highly customizable,2021-11-26
2, Cloud Flow ,Automates the process,2021-11-10
3, Model Driven ,Component based approach,2021-11-03

As you can see in the second output it is not enclosed in quotes.

For this issue, we need to handle it with an expression

//Expression to enclose string in quotes, if it has comma
if(equals(indexOf(items('Apply_to_each')?['Description'],','),-1),items('Apply_to_each')?['Description'], concat('"',items('Apply_to_each')?['Description'],'"'))

The created output can be created as a file or directly it can be passed to email as attachments

The limitation in the second approach is, a string variable can hold up to 104857600 bytes. If it exceeds the size it will throw an error. Please have a look at how to add rows in excel and read csv file.

Please post your queries in the comments section. Happy Building 🙂