Many times when setting up a Microsoft Dynamics CRM 4.0 implementation it is necessary to import existing data into the new CRM system. Commonly, this data comes from Excel Spreadsheets, or the system it came from has a way to export it into Excel.
Microsoft provides two tools for importing data into CRM natively.  One, the Bulk Data Import Tool is built into CRM, the other, the Data Migration Wizard, is a separate install provided with the installation media for CRM. Both of these methods accept only one file type, csv, or comma separated values.
Each data import tool has it’s own pros and cons, and I have performed many migrations using each one, most recently the Data Migration Wizard. Some of the things I like about this option are:
- Several validation steps before the final import
- Detailed error descriptions including line numbers
- Ability to import cells as lookups or picklists
- Ability to choose which attribute of the related record you are importing (i.e. Account Name or Account Guid if you are importing Contacts with a Parent Account field)
- Picklist options are added on the fly if a matching value is not found
- The Data Validation before the Import will alert you if a duplicate lookup reference was found or no lookup reference was found
- Successful migrations are saved automatically
- Option to delete the data imported by previous migrations
- Ability to create missing custom attributes on the fly (this is extremely helpful when a large amount of custom attributes are required)
Some cons are:
- No ability to discard or merge duplicate records being imported
- Needs to be installed on a local machine, even for CRM Online implementations
When using the built in Bulk Import Tool, notable features are:
- The fact that it is built in to CRM means it can be run from any client computer, using web client or Outlook client
- Non administrative users can be given privileges to perform bulk imports, letting users import their own sets of data (obviously this can open the door to less than clean data being imported into the system–it is good to know the users who will have this privilege)
- Uses duplicate detection rules created in CRM to detect duplicates when importing–dependent on the CRM Asynchronous Processing Service therefore it can take longer to complete and slow down other system jobs.
- Lookups can only be correctly linked if an automatic data map is used, to use an automatic data map, every column name in the spreadsheet must be exactly matched to the Display Names in CRM
CSV files also have their pros and cons,
- Since commas denote cell breaks, having commas in your data will give you rows with different numbers of cells, which not surprisingly, will not import into CRM
- CRM gives you the option of Importing CSV files that uses a quotation mark as a data delimiter, therefore you could have a cell that looks like this: “Adventureworks, Inc.”, and the comma between Adventureworks and Inc would not be interpreted as a cell break
Although Excel will let you save your .xls or .xlsx files as .csv files, there is no way to delimit all of your data with quotation marks, which means you need to import spreadsheets without commas in the data, which is rarely a viable option
To help overcome some of the obstacles of using CSV files, I created a simple program that takes an .xls or .xlsx file and converts it to a quotation mark delimited CSV file. Commas embedded in the data are preserved, and carriage returns are replaced with dashes so that rows are not ended prematurely. I have made the program available for download, if you wish to have the source code to modify for your own uses, feel free to email me at mgronbeck@summitgroupsoftware.com and I will provide it for you. To use the program, run the installer, in the Start Menu or the directory specified, run the executable file, Browse to a Source File, a Destination Folder, click the Load Sheets button and select the sheet your data is in, and click Convert.
Note:Â if you do not have Excel installed, you may need to install the Access Database Engine, which is found here
Download the Excel Conversion tool Here
