Dataverse data export options

  • 5 minutes

Microsoft Dataverse provides several methods for exporting data from Dataverse tables:

  • Export from Excel in model-driven apps

  • Export with Word and Excel templates

  • Export data in Power Apps maker portal

  • Power Automate

  • Azure Synapse Link

Export from Excel in model-driven apps

Model-driven apps include the capability to export rows from a table into Excel.

Screenshot of Export to Excel options.

The Static Worksheet options download a data export of the rows and columns from current table view into an Excel file. You can export up to 100,000 rows at a time.

The Dynamic Worksheet option allows you to select the columns from the table and columns from other tables that have a many-to-one relationship. The Excel file contains a query not data. You can use the Microsoft Power Apps Excel Add-in to download your data, selecting Data > Refresh in Excel will export the latest data. The data that will be shown in Excel depends on your security access to the table.

The Dynamic PivotTable option is similar to the Dynamic Worksheet but creates an Excel PivotTable instead of a list of rows.

Open in Excel Online

The Open in Excel Online feature in model-driven apps displays the rows and columns from current table view and allows you to make quick edits, create new rows, and then save the updated information back to Dataverse.

Screenshot of Excel Online.

Data changes are imported in the background and you can track the progress of the import by using the Power Platform admin center under Settings > Data management > Imports.

Export with Word and Excel templates

Model-driven apps support the use of Word and Excel templates. You can use these to export data from Dataverse.

Screenshot of Excel templates.

You can download a template from a model-driven app, format the file, and then upload the file back into the model-driven app. You can then use the template to export data.

Excel templates are used with table views and you can specify the columns from the table when downloading the template. The template can also contain columns from tables that have a many-to-one relationship.

Word templates are used with a single table row but can include columns from tables that have a many-to-one relationship and also rows from tables with one-to-many relationships or many-to-many relationships. Word templates are used to generate structured formatted output such as an order.

Export data in Power Apps maker portal

The Export data option in the Power Apps maker portal is a simple export that extracts all columns and all rows for one or more tables.

Screenshot of Excel data in maker portal.

Select Export and then Export data from the button on the top menu, select the tables with the data that you want to export, and select Export data in the top right of the pane. Dataverse will export all data from the selected table into a Zip file. The Zip file is then available to download and will contain the data in a .CSV file for each table.

Note

Choice columns will only contain the numeric values and not the label and lookup columns will only contain the GUIDs of the parent rows.

Power Automate

You can use Power Automate cloud flows with the Dataverse connector to query table rows and generate output files. By using Power Automate, you can extract exactly the information that you require in the format that you need.

The Azure Synapse Link for Dataverse is a service designed for enterprise big data analytics by delivering scalable high availability with disaster recovery capabilities. Data is stored in the Common Data Model format, which provides semantic consistency across apps and deployments.

After enabling the Azure Synapse Link for Dataverse in the Power Apps maker portal, you choose the tables in Dataverse and Dataverse will perform a continuous replication of data to Azure to either an Azure Data Lake storage Gen2 account or an Azure Synapse Analytics workspace.

Diagram of Azure Synapse link.

The Azure Synapse Link for Dataverse provides these features:

  • Linking or unlinking the environment to Azure Synapse Analytics and/or Azure Data Lake Storage Gen2 in your Azure Subscription.

  • Continuous replication of tables to Azure Synapse Analytics and/or Azure Data Lake Storage Gen2 in your Azure Subscription.

  • Replication of both standard and custom tables as well as create, update, and delete transactions.

  • Fewer clicks to process, transform, and visualize your data in Azure Synapse Analytics.

  • Serverless data lake exploration, data integration, and big data processing in Azure Synapse Analytics.

  • Facilitated metadata discovery and interoperability between data producers and consumers such as Apache Spark, Power BI, Azure Data Factory, Azure Databricks, and Azure Machine Learning.

Note

Change tracking should be enabled on a table to allow the table to be included in the export.

The Azure Synapse Link for Dataverse service supports initial and incremental writes for table data and metadata. Any data or metadata changes in Dataverse are automatically pushed to the Azure Synapse metastore and Azure Data Lake, depending on the configuration, without any additional action. This is a push, rather than pull, operation. Changes are pushed to the destination without you needing to set up refresh intervals.