cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gikido
Helper V
Helper V

Export excel with maximum number of records

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)

1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

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.

vqiaqimsft_0-1669948916490.png

 

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.

Best Regards,
Qi

View solution in original post

2 REPLIES 2
v-qiaqi-msft
Community Support
Community Support

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.

vqiaqimsft_0-1669948916490.png

 

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.

Best Regards,
Qi
gikido
Helper V
Helper V

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,656)