Answered

Export to excel of SQL server data in canvas app

(0) Share

Report

Posted on by Saratha

Hi All,

I have customized the canvas app by Azure SQL data connection. I would like to export the data which are displaying in the data table. How to achieve it. For export to excel, I am using Excel Online of Onedrive business. But it has some limitation of file size. Is any other way to create the export to excel from Canvas App. Please suggest the solution.

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Saratha30_M ,

Do you want to export data from SQL to Excel by using powerapps?

Is the limit of Excel 2000 records?

If so, that should be caused by delegation problem.

However, the functions about updating are all not delegateable in powerapps.

So I suggest you an alternative way:
Import data from SQL to CDS. Then you could export data of CDS. The exported file is a compressed folder with csv file.

In this solution, you will not have the limit of 2000 records.

The steps should be like:

1)In CDS, choose get data:

2)choose Azure SQL

Import data.

3)After you have imported data to CDS successfully, export data.

Choose export data.

Choose the entity that you want to export and then click "export“.

After you've exported successfully, click "download....".

Then the download file is a compressed folder with csv file like this:

This is a excel file with the data of your SQL.

You could modify it to the format that you want and then upload it to one drive.

Best regards,
Saratha 30 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-yutliu-msft,

Thanks for your suggestion. I am not using CDS database. Instead of CDS database, directly connecting to SQL database for data manipulation. So I can't use CDS export data feature for export to excel.
Verified answer

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Saratha30_M ,

Could you tell me how many records in the data table that you want to update to the excel?

Since powerapps has delegation limit, you could only update at most 2000 records in one time.

So here are two solutions

1)save data in data table every two thousand records in one collection

Then update the collections data to the excel one by one

Do not forget to change the non-delegateable limit to 2000.

2)import sql data to cds (the way that I posted)

When you import data, you could use sql query to filter data to import.

Filter data to the data in the data table.

Then export the cds entity to cvs file.

This solution does not have excel record number limit.

Best regards,