For many years, a Microsoft Excel export and import redesign has been one of top change requests from the Microsoft Dynamics CRM Community. The previous version of the feature did not allow you to import large sets of data, extract more than 10,000 records without risky database hacks, and wasn't always user friendly. In today's blog, we will highlight ten key MS Excel Import and Export redesign features in Microsoft Dynamics CRM Online 2015 Update 1.
Note: Currently, these features are only available in MS CRM Online 2015 Update 1, but we are expecting it to be released for on-premises installations soon.
1. Redesigned Export Options
Microsoft Dynamics CRM Online 2015 Update 1 introduced a new design for the CRM Data Export feature. Now, instead of just one Export to Excel button, users will see several Export options, including the default option, Export CRM View in Excel.
The full list of data export options available with Update 1 includes:
- Static Worksheet/Open in Excel Online
- Static Worksheet on Page
- Dynamic Worksheet
- Dynamic PivotTable
The first three options provide the ability to import the exported file back, making the process of CRM data modifications even more streamlined. Now users can export data in one click, modify the data, and import exactly the same file back. No file conversions required!
*Source: Microsoft Release Notes
2. Once Click Export Feature
In addition to the advanced Export to Excel options, Microsoft now offers the ability to export data to MS Excel Online directly from a CRM View. Users can click on the Export to Excel button and the downloaded file becomes available on your desktop. No more popup windows asking you to select the file format! Again, this option is only available for CRM Online.
3. .xlsx Import File
The default CRM data export and data import format is now .xlsx. The exported file is automatically converted to an Excel table which can be open and modified by any software supporting Excel files. This is a fantastic enhancement that will save time for CRM users working with data on a daily basis!
- When data is exported from CRM, the Excel tab name corresponds with the selected CRM View.
- Column width scales from View Definition for a consistent display.
- Field format is now respected. Text stays as text, numbers are numbers, etc.
These changes will definitely improve the Excel export experience!
4. Enhanced Data Re-import
One of the most exciting changes in the redesigned CRM Data Export and Data Import is the ability to export CRM data in Excel format and re-import it back. Now, every export file brings the GUIDs associated with CRM records, and users can bulk edit and re-import the data back if required. Previously, CRM could only import data that was marked for data reimport. CRM is now intelligent enough to recognize the GUIDs of exported records and match them automatically when data is re-imported back to CRM.
5. Composite Fields
Another exciting feature is the ability to export composite fields in one column. Previously, CRM exported each field associated with a composite field in a separate column. Now, Full Name is exported as one column including First Name and Last Name. Address 1 is exported as one column as well. Please see details below*.
*Source: Microsoft Release Notes
6. Warning Dialogs are Fixed
In previous versions of CRM, CRM data export files were downloaded either in xls or xml format. When users opened the file in MS Excel, a warning message popped up every time. For example:
- The file format and extension of "XXXX.xls" don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?
- Be careful - files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View.
- External Data Connections have been disabled. Enable Content.
Now, with Update 1, these dialogs are completely removed and no longer appear on the screen! Depending on personal settings, users may still see the protected view messages, but this can be easily configured in individual MS Excel settings.
7. Immersive Excel Online: Ad hoc Analysis in MS Excel Online
Integrated MS Excel Online now supports the ad hoc analysis within CRM. Users can select a view to export data from and MS Excel Online will open the selected dataset right in a CRM window. No pop-up or local file copies!
In this view, users can create formulas and perform ad hoc and what-if analysis right from Excel Online within CRM. For example, you can summarize the total amount of open or won opportunities and quickly run what-if analysis by applying Excel formulas without leaving CRM.
- The ad hoc analysis feature is only available for System and Personal Views. Advanced Find, Quick Search and Filtering Views do not support the functionality.
- The default threshold value to refresh Excel Online files (generated in CRM) is defined to five minutes. If users do not save their changes in five minutes after the Excel file is opened, CRM will refresh the data in the file. We recommend saving changes before this limit is exceeded.
8. Immersive Excel Online: Bulk Updates in MS Excel Online
In the same view as above (Select a Viewà
Export in Excel Online), users can modify the presented data and save their changes directly to CRM in one click. The changes are processed based on the following rules:
- CRM uploads the exported and modified dataset as a new data import file that users with sufficient permissions will be able to see in Data Managementà
Imports. The bulk updates will have an Excel Online prefix so they are easier to differentiate.
- CRM maps Excel columns based on Display Name. This is the reason why a view with same column names (Display Names) cannot be exported.
- CRM honors the sequence of changes made in CRM and MS Excel. If another user updates a record at the same moment you exported a view and modified it, the CRM modifications will not be overwritten by changes in MS Excel.
- CRM updates records on a field-by-field basis. The bulk update feature only changes the fields that were actually changed in the Excel document. For example, if you export data in Excel Online and modify the City and Main Phone Number, then only these two fields are updated during bulk import.
- Column names in Excel cannot be changed if the file is to be imported back to CRM.
Note: The bulk update feature is only available for System and Personal Views. Advanced Find, Quick Search, and Filtering Views do not support the feature.
9. CRM Export Limit has been Increased to 100000
Users and administrators used to only be able to export 10,000 records. This limit was controlled by an internal CRM setting that System Administrators and Customizers could not adjust within CRM. Any modifications to the number of records that users could export from CRM required a direct database update.
With CRM Online 2015 Update 1, users can now export up to 100,000 records with just one click! This increased limit will prevent users from having to update the database directly, which is never a great option. No additional customization is required!
Note: The exported file cannot exceed 32MB in size.
10. CRM Data Import File Limit has been Increased to 32 MB
Originally, the file size could not exceed 8MB if not zipped and 32MB for zipped files. Now, the limitation is gone, and you can upload up to 32 MB! No more hurdles of splitting a data import file into several XML or CSV files!
BONUS CONTENT: Microsoft Dynamics CRM Online 2015 Update 1 - Data Export Formats
In previous CRM versions, data exported from CRM was formatted as Text Columns regardless of the CRM field type (Text, Whole Number, Currency, Date, etc.). If a field had a leading zero, it would disappear in the exported file, which resulted in invalid exported values. For example, Account, Contact, and Order Numbers would have a completely different unique value once exported to Excel. This made the ability to import data back into CRM complex and cumbersome.
With the newly redesigned Export to Excel function, CRM exports data preserving all formatting. When data is exported from CRM, the Excel tab name will correspond to the selected CRM View and Column Width scales from the View Definition for a consistent display.
The key enhancement with data mapping rules in Export to Excel functionality is that now CRM retains the data format for the exported data. CRM will export data according to the configurations of CRM Views and metadata and will honor different data formats. The table below describes the new mapping rules for exporting data in Excel*.
*Source: Microsoft Release Notes
These 10 features of the redesigned data export and data import dialogs have improved the overall data management experience in MS CRM. These exciting integrations with MS Excel Online provides a seamless experience for end users! You can learn more about the updates for CRM 2015 by visiting our website.