Bulk Update Records with Export/Import Dynamics CRM 2011

Paul Nieuwelaar, 20 March 2012

In this blog post I will be looking at easy it is to update massive amounts of records in Dynamics CRM 2011. The idea is, we have hundreds of records in our system, and now we want to update them with new data. We may have added a new field that we need populated, or we could be transferring data from somewhere else in the system or from another system.

If your requirements are simple, such as setting 1 value across several records, then using the ‘Bulk Edit’ feature is probably best.

If they are more complex, such as updating hundreds (or thousands) of records, where bulk editing 250 at a time may not be feasible, it is probably best to perform an export to excel, and then re-import your changes. By doing this, you can also utilise the native Excel formulas, such as setting a value based on another. You can also use vlookup to pull through data from other spread sheets, which is useful when matching data from other systems.

To begin exporting your data, it is usually a good idea to run an advanced find to get your records. By using advanced find you can customize the columns to add the required fields, and remove any columns that are not required for this export. You can also add filters to the advanced find to query only certain data if you do not want to update every record, for example if you only want to export accounts with a relationship type of Customer.

In my example, I will export all my Accounts which have no Country set, so I can then set them all to New Zealand. Since all my customers are in New Zealand, and because I have over 600 Accounts, using Export to Excel is the best option.

After running the Advanced Find query, click on ‘Export’ from the ribbon. In the dialog that appears, select to export ‘records from all pages in the current view’. At the bottom, you will also need to check the box to ‘make this data available for re-importing’. By checking this box we will be able to update the existing records later when we re-import, otherwise it would create duplicates.

Once you click Export, you will be prompted to save an XML file. Save this somewhere, and then open it in Excel.

NOTE: If the file does not open in Excel you may need to right click the file, select Properties, and then click the ‘Unblock’ button under the General tab.

At this stage it’s usually a good idea to keep a backup of the XML file somewhere in case it goes wrong later on. You can now go through and edit the data using Excel formulas to set conditional values, or just to set static values such as the Country.

Be VERY careful when editing this data in excel. You may have noticed there are several hidden columns, these are used to store the record ID’s and last modified dates (so you don’t wipe any recently updated data). It is very important that you don’t change any of these. If you need to apply any sorting to the columns, you should do this through the advanced find before exporting to excel. The order of records in this spread sheet should not change.

When you’re ready to re-import, go back into CRM and click on ‘Import Data’ from the ribbon of your entity view. Browse to the modified ‘For Re-import’ XML file, and then step through the wizard. CRM will detect that it’s a re-import, so you won’t need to select anything else.

After clicking Submit, the records will begin being updated. You can track the import progress from ‘Imports’ under Workplace. If all goes well, you should see 100% of your records were successfully imported, and you will immediately see the updates throughout your records.

We can see here one of our updated accounts with the new Country value: