Create CSV or Excel files from Power Automate

If you work with Power Automate, you will occasionally need to create CSV or Excel files. In this article, I will show the following:

1. Create a simple CVS file from the result of the SQL stored procedure and save it in OneDrive folder
2. Create an Excel file while using the template and bring the data from SQL SQL-stored procedure

Let's explore both options.

Option # 1: Create CSV File: the easiest option by far.

Few notes:

1. CSV file is the easiest to create.
2. You can not use any sort of templates with CSV files.
3. You can either create column names or choose automatic column names instead

STEPS:

1. Trigger the procedure
2. Execute the SQL stor.proc
3. Parse JSON: Different JSON structures will affect what you should use in the next step. Whenever I work with SQL-stored procedures, the JSON file looks like this:

4. In this case, to create a CSV table, you have to ensure that you are not just simply selecting the body of the action prior, but that it has to have this syntax

body('Parse_JSON')?['ResultSets']?['Table1']

5. The last step is to save the file. You can either email it or upload it to OneDrive or Sharepoint. Here is a quick code on how to save it to OneDrive in a new folder:

JobID - > is a parameter passed at the beggining of the flow

File Content - > body ("Create_CSV_table")

Final Flow

Option # 2: Create an Excel file: this requires a little bit more work

Few notes:

1. I am calling the template to be used to create a file. The template CAN NOT contain any special characters. If you do have them, you cannot write back in the file. In my case, I had to replace "Part #/ Item" with "Part Item" to make it work. The data in the template will have to be formatted as a table; in my case, the table name was Table1.

STEPS:

2. I am passing the data by running the SQL stored procedure.

3. After running the store procedure, create a new Excel file: the File Content is coming from the action above: body('Get_File_Content')

4. Parse JSON the same way as for CSV files

5. Optional step: Use Compose step to write back to the file:

6. We have a dynamically created Excel file, so the name will also have to be passed dynamically as the following ( in this case):

concat('/',triggerBody()['number'],'/',outputs('Create_file_2')?['body/Name'])

7. Row will be the output from the Compose Step above.

Done.