Sunday, January 4, 2015

Import data into Dynamics CRM from Excel files

On my CRM wiki, I added a page that describes how you can use the Open Source ClosedXML project to run Excel formulas from CRM workflows.

Another way to use ClosedXML in a CRM plug-in is for importing data directly from an Excel file.
I implemented this recently for a client who has several employees who need to import data on a daily basis. They wanted to build out the data in Excel (multiple entities, picklists, custom matching logic, etc.) and quickly get the data into CRM, without the need to export the data first before importing.

One possible design for this is to register a plug-in on the Create message for an Annotation (note) record. The user submits the Excel file as an attachment. The plug-in code gets the message body (Excel contents) from the attachment and passes the data as a Stream to ClosedXML, which loads the Excel contents into an easy-to-query object model. From there, it's simply a matter of getting the cell values and performing your CRUD operations in CRM. For the plug-in, you'll need to use ILMERGE to combine your plug-in assembly with ClosedXML.DLL and Microsoft's Open XML library and register that combined/merged assembly to CRM.

Note that this solution works in CRM Online as well as on-prem. Some .NET assemblies can't run in the Sandbox because of platform constraints in CRM Online but ClosedXML works fine.

(Note: After I wrote the above info, I found an interesting CRM forum post relating to importing data from Excel into CRM. The structure of the data the person presented in the question is not in rows and columns, but that's not a problem because the solution I outlined above can be used to import this type of "unstructured" data without too much effort. Once the Excel content is in the ClosedXML object model, you can reference any cell, range of cells, calculation, etc., so obtaining the data no matter how it's formatted is certainly possible.)