Save time when you import leads, contacts, and accounts
Published: March 9, 2007
When you have data that you’d like to import into Microsoft Dynamics CRM, it can be challenging to figure out where to start, and to understand why some data doesn’t go in as smoothly as you might expect. The basic concept is simple: somewhere in the import process you have to match each column in your data with its destination in Microsoft Dynamics CRM. The Bulk Import Wizard offers one way to do this matching: it lists each of your source columns, and for each one, you select the appropriate attribute in Microsoft Dynamics CRM.
It isn’t quite as simple as it looks, however, because your data must be in the format Microsoft Dynamics CRM expects, and there are 40 or more choices of attributes to scan through for each of your columns.
There is another way to do the mapping that some people find easier than picking each destination from a long list. That is to rename the columns in your source file before running the Bulk Import Wizard. This technique speeds up the import process, and makes it easier to check in advance if you have all the required columns and if the data is in the expected format.
Make sure the first row in the file contains column headings.
3.
Make sure each column heading matches the display name of an attribute in Microsoft Dynamics CRM. Display names are case-sensitive, so your column heading needs to match the display name exactly. There are two ways to find the attribute display names:
1.
This article contains tables with the display names for attributes which are visible in the default account, lead, and contact forms.
2.
If your system has been customized, use the procedure at the end of this article, “Identifying Display Names for All Attributes.”
4.
Make sure there is a column in your source file for each required attribute, and that each row contains data in each required column. Use the tables in this article to identify the required attributes.
5.
Compare the data in each column in your source file to the data type for each Microsoft Dynamics CRM attribute as listed in this article. The data in your file must match the data type of the attribute. For example, importing a record will fail if you have text in an attribute that requires an integer, or if the date is not in the required format.
6.
Compare the length of each text column against the maximum length as listed in this article. Importing a record will fail if you have a value that is longer than the maximum length for that attribute.
7.
If your file has international characters, in Excel, click Save As, and then select Unicode Text (*.txt).This saves the file as tab-delimited. When you use the Bulk Import Wizard, on the first page, select Tab (\t) in the Field Separator list.
8.
If your file does not have international characters, in Excel, click Save As, and then select CSV (Comma-delimited) (*.csv). When you use the Bulk Import Wizard, on the first page, select Comma (,) in the Field Separator list.
The values in your source file must be mapped to valid list values for that list in Microsoft Dynamics CRM. You can either replace the values in your source file by doing a global search and replace, or wait and let the Bulk Import Wizard guide you through the mapping. In either case, it is a good idea to review what the valid list values are before doing the import.
The values in your source file must be mapped to valid list values for that list in Microsoft Dynamics CRM. You can either replace the values in your source file by doing a global search and replace, or wait and let the Bulk Import Wizard guide you through the mapping. In either case, it is a good idea to review what the valid list values are before doing the import.
Importing data into lookup columns is possible, but requires looking up the globally unique identifier (GUID) for each record prior to doing the import. For example, for each account to be associated with a specific Parent Account, you need to look up the GUID for the account record of that Parent Account, and then paste this information into the line for the contact in your import file.
If you need to import related records, we recommend using the Data Migration Pack, rather than the Bulk Import Wizard.
Look up a GUID and paste it in your file
There are two techniques you can use to find a GUID:
1.
Copying the GUID from the Internet Explorer address bar
2.
Installing a free, unsupported tool that displays the GUID
Copy/paste the GUID from the Internet Explorer address bar
1.
Open the record in Microsoft Dynamics CRM.
2.
Press F11.
3.
Copy the GUID from the Address box of Internet Explorer:
1.
Select the GUID part of the address. It will look like this: {66374D9D-C9DB-DA11-8F20-000BDB5C46AB}
2.
Right-click, and then click Copy.
4.
Paste this in the correct row and column in your source file. Be sure to include the closing brackets.
Use a tool from the Microsoft CRM Sandbox
As an alternative, there is a free tool posted on the Microsoft Dynamics CRM Sandbox (http://codegallery.gotdotnet.com/CRM) on the download page called MSCRM 3.0 – Bulk Import – Guid Solution (posted 2/3/2006). This tool is unsupported, so use it at your own risk. It must be installed by a person with the Microsoft Dynamics CRM System Administrator security role.
Note
Importing data into the Parent Customer attribute
There is one lookup field in the Contact record type, Parent Customer, which is a special case. This lookup can either refer to an account record or a contact record. You’ll need to separate your import file into two files: put all records that have parent accounts in one file, and all records that have parent contacts in a separate file. Then, when you’re in the Bulk Import Wizard, specify which record type you are using, so that the wizard knows which record type to validate the GUIDs against.
Dates must be in the format specified in Microsoft Dynamics CRM on the Calendar tab of the System Settings page. This page is accessible only to people with the System Administrator or System Customizer security roles.
If you don’t have one of those security roles, you can identify what this setting is by clicking Activities in the Workplace. Look at the format of the date in the Start Date column. Use that format for all date columns that you are importing. You can import just the date, or you can include a time, as long as you match what is displayed.
The tables in this article show only the default Microsoft Dynamics CRM attribute names. To see the complete list of attributes that you can use as column headings in your source file:
1.
Create a small sample text file with one column, and one line of data.
2.
Use Notepad to make a file called sample.txt:
First Name
Joe
3.
In Microsoft Dynamics CRM, on the Tools menu, click Import, and select the file.
4.
Select the record type that you want to know the available attributes for, and then click Next. Find the row that contains the First Name source field. Look at the values in the list in the Target Field column for that row. This list shows all the possible attributes that you can use as column headings in your source file.
Note