Working with data in the Common Data Service for Apps using the Excel Add-in!

 

Getting data in and out of the Common Data Service for Apps is a fundamental task when building apps and flows, we’re happy to say the Import, Export and Open in Excel features are now available for the latest update of the Common Data Service for Apps. These features have been released in addition to the PowerQuery functionality already available in environments today. Working with data can take may forms, hence why we have multiple methods for importing, exporting and manipulating data within CDS for Apps. This blog will focus on the importing and exporting data from Excel and CSV files, and interacting with data using the Excel Add-in.

Before we do, if you’d more information on the existing tools:

• PowerQuery is used to get data and transform it from a number of cloud services, and create entities as part of a single step while importing data. Find more information here.
• Data Integrator is used setup reoccurring data integrations between systems, typically managed by an admin to integrate data from other systems of records into\out of the Common Data Service for Apps.

Excel Add-in

The PowerApps add-in for Microsoft Excel allows you to open an entity in an Excel worksheet to view and edit your data. The Excel add-in allows you to refresh your data, create new records, delete and edit existing records all from within Excel. You can read more about the Excel add-in here.

To get started, go to web.powerapps.com and select the environment you’d like to use (If you don’t already have an environment, follow the steps here. You can open the Excel Add-in by first opening your list of entities (Data > Entities) and selecting an entity from the list, from there you’ll see the Open in Excel appear on the command bar.

Clicking this will generate a new Excel file and prompt you to download it, you can download the file onto your local drive, or OneDrive. Once you’ve downloaded and opened the file you’ll be prompted to enable add-ins, don’t worry at this point if the file contains no data – it needs to connect to your environment to refresh the data first.

Once the workbook has loaded, you’re now connected to your CDS for Apps environment and ready to start making updates. You can find a detailed guide here. Here are some quick tips to keep in mind:

• The Excel add-in connects to CDS using your credentials, the same permissions that would apply if you were using an app or flow, would apply here. If you don’t have permissions to access an entity, you’ll need to get access from your admin before using the add-in.
• You need to click “Publish” to have your changes published back to CDS, if you don’t click publish, they will not be reflected in other apps.
• Lookups are presented in the Excel workbook as two columns – the Display name, which is the friendly field you typically use to enter the name or primary identifier and the GUID, which is the system generated unique ID. The Excel add-in helps you update your data by presenting you a list of lookup values whenever you select a lookup field (either the Display or the GUID) but keep in mind – that as the Display isn’t always unique, you should avoid copy and pasting into this field as we won’t be able to match it against the record you’re trying to enter. If you need to copy and paste multiple lookup values, you should use the GUID field.
• The workbook will refresh by default when you open it – so make sure you publish your changes regularly, and before closing the workbook.

For more information on the Excel Add-in, you can find a detailed guide here.

Importing and Exporting Excel and CSV files

We are also reintroducing the ability to bulk-import and export data from Excel or CSV files by using the Get Data from Excel file and Export Data features for updated Common Data Service (CDS) for Apps environments. With this feature, you can move data into single or multiple entities from Excel or CSV files. Alternatively, you can export data to a CSV file from single or multiple entities. Import and Export from Excel is accessible from the command bar by going to Get Data > Get Data from Excel, or Export > Data.

The import and export from Excel is a great option for importing and exporting data when:

• You’re working with a file on your local desktop, not on OneDrive for Business or other cloud location.
• You need to import or export multiple files at the same time.
• Your entities contain relationships to other entities and you want to use GUID or your primary keys to set the lookup (foreign key) on import.
• You need to export your data to Excel or CSV files and don’t require a live connection back to your data.

 

You can find a detailed guide of how to import and export data here.

Helpful resources

• Open Entity data in Excel
• Import and Export data from CDS for Apps
• Create a new environment for the Common Data Service for Apps
• Common Data Service for Apps Overview

As always, if you have any feedback please reach out on our forums.

Thanks,

Clay.