Today I will be explaining about the new design of Import/Export feature in Dynamics CRM. Also I will introduce you with Immersive Excel Experience released in Update 1 of CRM 2015.
Challenges in old design
Before introducing to new enhancements in Import/Export design of CRM 2015, we will go through challenges users are facing in old design. Below is the list of the same.
- When users export any view from CRM, the file is downloaded either in xls or xml format. Hence, when users open the file in an Excel, a warning message pops up every time.
- When users export any view from CRM, the data from all the columns are treated as Text columns, i.e. irrespective of your CRM column type (Decimal, Whole Number, Currency, Date & Time) all data is considered as Text.
- Leading zeros disappear in exported data i.e. if your data any number starting with 0, those zeros don’t appear in Excel. This could represent incorrect data for e.g. if your Order number is 0021578, then in Excel it will show as 21578 which could mislead to users analyzing the data.
- Composite columns are downloaded as multiple columns in exported data i.e. Full Name column of Contact entity is exported in 3 columns (first name, middle name & last name).
- Whenever users click Export button in CRM, an Export wizard pops up with options like whether to export records from current page or all pages of view and whether the exported data should be in re-importable format or not. This is an unnecessary step users have to perform for sample data export as well.
Enhancements in Import/Export capabilities
Microsoft has released plenty of features in this release by overcoming above challenges. Below are the enhancements:
- One click export – The wizard is gone. Now users just have to click on Export option and CRM exports all records from all the pages. The data is always exported in re-importable format. Also, the file is downloaded in XLSX format.
- Warning message is gone – As the downloaded file is in XLSX format, the warning message is gone.
- Data format is retained in exported data – Now, export features read configuration from CRM views and metadata and the data format is retained in exported Excel. Also the exported data is represented as Excel Table and width of the columns are also retained in downloaded file.
As shown in above image, the exported data format is retained in the Excel file. CRM uses display names of exported file for mapping purpose while re-importing the data back into CRM. Hence it is always recommended to keep the column names unchanged.
Below table shows how CRM data is represented in Excel:
- Maximum record limit is 100000 – Microsoft has increased limit on number of records that can be exported in Excel file. It has been increased from 10K to 100K. Also now file size limit to upload data in CRM is increased to 32 MB.
- Users can export Calculated/Roll up fields in Excel.
Note: Users can edit these fields in Excel file locally, but the changes cannot be imported back into the CRM.
Introducing Immersive Excel Experience
In this release, Microsoft has integrated Excel Online capabilities in CRM. It allows users to work on data without moving away from CRM. Below are the features of Immersive Excel capabilities:
- Ad hoc analysis – As Excel Online is integrated into CRM, users don’t have to move away from CRM. Users can open any public/personal view in Excel Online and take benefits of Excel Online capabilities to work on CRM data.
- Bulk Edit is possible – Excel Online integration allows users to modify the data right within the CRM and send the data for bulk update. This process runs asynchronously and users can see the import status in logs under Settings -> Data Management -> Imports.
In above image, you can see an option to save data into CRM. Also the data is opened in Excel Online right within the CRM. As now users can open data in Excel Online, and also data can be updated within the same, need of any third party Inline Editable Grids is gone.
Limitations in Immersive Excel Capabilities
As Microsoft has integrated Excel Online in this release, there are few limitations which need to be considered. I have listed below the same:
- It is available only for CRM Online versions.
- Due to technical limitations, ‘Save As’ option does not work in Excel Online.
- It only works in web browser. It will not work in Outlook for CRM and tablets.
- To take advantage of this feature, views must be saved in CRM. It will not work in Advanced Find views.
Note: If you have any advance find views and you want to use this feature, you can save the view as a personal view and then use this feature.
- This feature is not available for sub grids.
- The threshold value to refresh Excel Online file (generated in CRM) is set to 5 minutes. i.e. after every 5 minutes the file will be refreshed automatically and hence it is always recommended to save your changes periodically in CRM before this limit is exceeded.