cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How do I export the Contents of a Data Table to Excel

This should be an easy one but I haven't seen a solution explained for this anywhere yet.

 

Very simple, very common use case:  I have a data table that is showing 3 to 5 columns and 5 to 10 rows of data.  I have a button control that a user clicks and when they click on this it exports the contents of the data table into excel, or CVS.  How do I achieve this?

 

I have researched the export control, but I haven't found a solution.  If I point the export control to use the data table as the "data" field, then it exports into a zip file 3 files, none of which have any format compatible with a standard rows / columns excel format.  So there must be something I am missing here.

 

Again, fairly common use case:  Just a data table, and a button that exports this to excel.  How do I do this?

 

Thanks, 

 

Ian

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: How do I export the Contents of a Data Table to Excel

There is no way to directly export a datatable to excel from PowerApps. You can create a blank excel table with appropriate columns and create a connector to it and then add a collection to it. This thread in the ideas forum may be relevant Export-Collection-to-local-drive-as-CSV-or-Excel/idi-p/68559 

I have no experience with model driven apps but apparently there is a way to export data from them to excel.

https://docs.microsoft.com/en-us/powerapps/user/export-data-excel 

View solution in original post

8 REPLIES 8
Highlighted
Super User
Super User

Re: How do I export the Contents of a Data Table to Excel

I have done this using a gallery but a DataTable should work just as well. For example, lets call the DataTable, 'mydatatable'.  In Excel, create a spreadsheet with the identical column names as the mydatatable columns.   In Excel, give the table a name using the Design tab, something like 'mydata'. Save the Excel file to OneDrive, DropBox, etc.  In Powerapps, locate the Excel file and add the mydata spreadsheet from the Excel file as a DataSource.  Set your button OnSelect to Collect(mydata, mydatatable.AllItems) or ClearCollect(mydata, mydatatable.AllItems), depending on whether or not you want to append or replace the data in your Excel file.

Highlighted
Community Support
Community Support

Re: How do I export the Contents of a Data Table to Excel

Hi @Iantg,

 

What @Drrickryp suggested is the usual way to use to work with Excel file within PowerApps. You need to first connect to the Excel table as the data source in PowerApps. Yet I suspect what you are trying to achieve is that create an Excel file using PowerApps when clicking on button. If so, then so far there is no direct way to do this. The closest way I could find is that create csv file using flow:

https://powerusers.microsoft.com/t5/General-Discussion/how-to-export-data-from-powerapps-to-excel-by...

 

捕获.JPG

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: How do I export the Contents of a Data Table to Excel

Hi Thanks for the quick feedback everyone.  I suppose I should clarify the use cas a little more.

 

The powerapps data table is using a SQL Server view / table as the data source.  The user looks on the screen and sees the data in the powerapps data table, then wants to get a copy of what he / she sees downloaded to excel.  So there is a button on the screen that says "Export to Excel".  The user clicks this button, and the data table exports it's rows / columns into a zip file, or an excel file and user downloads it from here.

 

I know you have an export control in powerapss, and I know you can set this to look at various data sources and it will initiate a zip drive download...  But it doesn't quite work as expected.  It creates 3 files, none of which are traditional cvs, excel types of grid structures with columns and rows.  

 

I am aware that if the powerapps data table in question is using an excel spreadsheet as the data source, then users can just work between that excel file and powerapps...  But that is a different use case.  I am also not trying to email anyone, or attach anything to an email or get involved with flow or any other 3rd party tools.

 

I am sure this is one of the most common use cases.....  Just a simple method to export what is in the data table into a spreadhseet for download.  I have hard coded this in the past with visual studio, just not sure how to get started here.

 

Any thoughts or exacmples would be appriciated.  If this is functionality the development team over there hasn't thought of I would be shocked.  But if this is the case...  let me know so we can bump this into their suggested enhacements list.

 

Ian

Highlighted
Frequent Visitor

Re: How do I export the Contents of a Data Table to Excel

I'm bumping this because I never got a solution. If there are no viable solutions for this requirement, consider this post a feature request.  (Allow users to Export the contents of a Data Table to Excel)

 

Thanks,

 

Ian

Highlighted
Microsoft
Microsoft

Re: How do I export the Contents of a Data Table to Excel

Hello,

 

I'm having the same issue here. Is there a way to simply export a data table into excel?

 

-Andres

Highlighted
Super User
Super User

Re: How do I export the Contents of a Data Table to Excel

There is no way to directly export a datatable to excel from PowerApps. You can create a blank excel table with appropriate columns and create a connector to it and then add a collection to it. This thread in the ideas forum may be relevant Export-Collection-to-local-drive-as-CSV-or-Excel/idi-p/68559 

I have no experience with model driven apps but apparently there is a way to export data from them to excel.

https://docs.microsoft.com/en-us/powerapps/user/export-data-excel 

View solution in original post

Highlighted
Regular Visitor

Re: How do I export the Contents of a Data Table to Excel

Did you ever get this sorted?

 

The way is add a view on the table, call the view with a flow and trigger it from your PowerApp. I've over simplified the explanation, but essentially you can get it to email you the table as an excel file.

Highlighted
Frequent Visitor

Re: How do I export the Contents of a Data Table to Excel

Hello,

I had a similar situation where the data is stored in a SQL DB. I get the data that I need from the DB into a collection which has the same field names as that in the SharePoint list (this is just a throw away list to store the data temporarily) so its easy to push to ex:

ClearCollect(CollectionName, ShowColumns('[dbo].[TABLENAME]', "Field1", "Field2", "Field3", "Field4" ))

Then I have a button that when pressed first deletes all of the entries in the SharePoint list, writes the collection with the latest values up to the SharePoint list then downloads an .iqy file which I have stored in a document library which then displays all of the contents in the excel file. The .iqy file I got by going to the SharePoint list and clicking the Export to excel button then just saved it to a document library then point to the file. The onSelect property for the button is:

RemoveIf(SharepointListName, !IsBlank(SharepointListName));

Collect(SharepointListName,CollectionName);
Download("https://company.sharepoint.com/sites/sitename/exportdocumentlibrary/export.iqy")

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,306)