Export excel with maximum number of records

[gikido]

Hello everyone
I created a canvas application from a sql database which contains tables with more than 5000 records, I have to export an excel file from the galleries which contain filters. I managed to export it but the problem is that it only contains the first 2000 recordings. Is there a solution to have all the records on the excel file? (Note that there is no sign of delegation)

[v-qiaqi-msft · Accepted solution]

Hi @gikido,

If you want to export records more than 2000 from SQL to Excel in Power Apps, you could create csv table based on the collection you generated from Power Apps, here I will show you how you generate collection to save more than 2000 records.

Note that we will pass one collection to multiple collection (Each collection having 2000 records). Based on record count dynamically create collection and passed to Power Automate flow.

Here is the detailed steps:

1). Set a variable to count the RecordsCount/2000, you could set the OnStart property of the App as below:

Set(_count,3)

2). Generate multi collections as below:

Switch(
    _count,
    1,
    ClearCollect(
        colUpto2000,
        ShowColumns(
            Filter(
                Stage,//This is my data source
                Count <= 2000//Here is the column to displat the record index
            ),
            "ID",
            "Title",//Here is the column you want to export to excel file
            "Item"
        )
    ),
    2,
    Concurrent(
        ClearCollect(
            colUpto2000,
            ShowColumns(
                Filter(
                    Stage,
                    Count <= 2000
                ),
                "ID",
                "Title",
                "Item"
            )
        ),
        ClearCollect(
            col2001To4000,
            ShowColumns(
                Filter(
                    Stage,
                    Count > 2000 And Count <= 4000
                ),
                "ID",
                "Title",
                "Item"
            )
        )
    ),
    3,
    Concurrent(
        ClearCollect(
            colUpto2000,
            ShowColumns(
                Filter(
                    Stage,
                    Count <= 2000
                ),
                "ID",
                "Title",
                "Item"
            )
        ),
        ClearCollect(
            col2001To4000,
            ShowColumns(
                Filter(
                    Stage,
                    Count > 2000 And Count <= 4000
                ),
                "ID",
                "Title",
                "Item"
            )
        ),
        ClearCollect(
            col4001To6000,
            ShowColumns(
                Filter(
                    Stage,
                    Count > 4000 And Count <= 6000
                ),
                "ID",
                "Title",
                "Item"
            )
        )
    )
)

3). Create a flow as below, note that you just need to select the Ask in Power App from the trigger within the Compose.

4). Set the OnSelect property of the button that triggers the flow as below:

ExportExcelFlow.Run(     
JSON(
Sort(
col4001To6000,
Count,
Ascending
),
JSONFormat.IncludeBinaryData
),
JSON(
Sort(
col2001To4000,
Count,
Ascending
),
JSONFormat.IncludeBinaryData
),
JSON(
Sort(
colUpto2000,
Count,
Ascending
),
JSONFormat.IncludeBinaryData
)
)

Note that ExportExcelFlow is the flow name.

[gikido]

Hi @v-qiaqi-msft 

Thank you for your answer, it's really great.
I will try this method and I will come back to tell you the result.
thanks again