PowerApps Save as CSV or Generate Report from CSV/Excel

Introduction

I often come across where the business wants to have a report in Excel format. But the question is could we save as csv using PowerApps? Then with the help of Power Automate, we can. There are a lot of requirements that could be achieved in PowerApps with the help of Power Automate.

The training department uses the training App. It is for to create a report on all training. They need a provision to download a report on a button click.

Scenario

We have a list of data that needs to be exported as a report and we shall see how to save the report in Csv from PowerApps.

Objective

Power Apps can convert table data from a collection or data source into a CSV file that the user can open in Excel.

In this article, I'll walk you through using Power Apps to export a file to Excel.

Step 1. Creation of SharePoint list

Create a new SharePoint list that will store the details of training.

Step 2. Add A Table To The App To Show Information

Launch Power Apps Studio, then start from scratch to develop a new app. Put a label with the title at the top of the screen.

Step 3. Add Datasource

Add the TrainingList SharePoint list as a datasource.

This list of training can be viewed by inserting a data table and updating the attributes of the items to "TrainingList". We can delete the columns (datacards from PowerApp) that are not required.

Step 4. Setting Up A JSON Sample For The Excel Flow Export

In the above screenshot, We need a means to send data from the table into a flow as Power Automate will be used to build the CSV file. To achieve this, we will transform the data into a JSON format. We must create a sample of the JSON being supplied in order to get ready to create the flow. Make a fresh, empty screen, then add a button on it.

Step 5. Add code on the button onselect attribute

Add this code to the button's OnSelect attribute. Table data from a collection or datasource can be converted into text formatted as JSON using the JSON function.

Set(
    varJSONTraining,
    JSON(
        ShowColumns(
            FirstN('TrainingList', 2),
            "ParcelNumber",
            "Title"
        ),
        JSONFormat.IndentFour
    )
)

Step 6. Pass the variable on a label to show the results

After that, add a label on the button's side and enter this code into the Text attribute.

This is the JSON-formatted text that will appear on the label. That's it for now; let's get started building our flow. This JSON sample will be used shortly.

Step 7. Creating The Export To Excel Flow

We can now create the flow to export the data from the PowerApps left navigation pane. Select Power Automate from the Action tab. Next, select "Create New Flow."

Rename the flow name from Untitled to meaningful name.

Name the flow TrainingDataTable and click Save. We want to use the PowerApps (V2) trigger because it we can manually define inputs and their types. This PowerApps(V2) trigger gets added automatically when we create the flow from the PowerApps tab.

Step 8. Create a flow with all the steps

Put all of the stages listed below into a flow.

After selecting the "generate from sample" option, copy and paste the JSON that we previously created in Power Apps on the label control beside the button.

A new document library called Documents(Shared Documents) has a folder created by ExporttoExcel. FormatDateTime has a formula and even a concat function.

If a CSV file with the same name already exists, The Flow won't produce a new one. We can ensure that this doesn't occur by including a timestamp at the conclusion. This is the appropriate flow expression to utilize.

formatDateTime(utcNow(), 'yyyyMMddhhmmss')

To enable the CSV file to handle special characters (accented letters, currency symbols, etc.), we wish to encode it in UTF-8 format. This is to show the Euro symbol (€) appropriately. Use this code to resolve the problem. The three-character prefix "Create_CSV_table" in front of the body indicates that Power Automate should use UTF-8 as the format.

concat(uriComponentToString('%EF%BB%BF'),body('Create_CSV_table'))

Step 9. Connect The ‘TrainingDataTable’ Power Automate To Power Apps

On the Data Table screen, add a button to download and write the code that we had prepared earlier.

On the button select Write the code shown below. Remove the existing value which is present by default and keep only what is given below.

The code written on the button will convert the table data into a JSON, initiate and run Power Automate to return a CSV file, and then download the file to a local drive. From there we can open the CSV file in Excel.

Conclusion

In the above article, we saw how to export data tables to csv using Power Automate and PowerApps. It works like a charm. We can use this feature for reports.