The Microsoft Dynamics CRM Team Blog
News and views from the Microsoft Dynamics CRM Team
Creating thousands of records one by one in CRM is a tedious job and very time consuming. MSCRM v3 provides solution of this problem as its Bulk Import feature. Bulk import is a tool in MSCRM v3 to import multiple records of an entity to CRM system in one go. In this blog post I am going to cover usage and navigation of Bulk Import wizard with many hidden powers and mysteries of this tool.
To walk through the story we are going to import some leads here. We have an Microsoft Excel file with a lot of customer information about new leads and we have to create each record in CRM. Version 3 now supports bulk import for Account, Contact, Lead and Campaign Response entities. As the Bulk Import tool doesn’t support XLS files, we will save this Excel file as CSV file with name leadData.CSV. The initial two rows of our CSV file will look like
Contact Number,Lead Source,Reference ID,Company Name,Last Name,Subject
Contact Number,Lead Source,Reference ID,Company Name,Last Name,Subject
Bulk Import Wizard
The Bulk Import tool is a pretty simple yet a powerful tool to import multiple records to the MSCRM system. It can be launched from Tools -> Import in Web and from CRM -> Import in Microsoft Outlook.
Source Import File: This control allows a user to browse and select file to be imported. Select the file which contains records you want to import. This tool supports only csv and TXT files. In our case our source file is leadData.csv.
Record Type: This control allows user to select the entity from the dropdown where he wants to import the records present in the file. Bulk Import allows you to import for Account, Contact, Lead and Campaign Response entities only. We selected Lead here as we are importing records for Lead.
Field Separator: The Field separator is the character that separates data in a row. This control allows user to select a field separator from dropdown according to his file settings. Default value for this is Comma (,) but you can change it to Tab(\t), Semicolon(;) or Colon (:) according to your file. In our case the file data looks like “…,Microsoft,Glass,Tech-Fest” where data is being separated by Comma(,) hence we left this dropdown intact.
Field Data Delimiter: Sometimes the Field Separator character appears within data also. To keep this character as data and NOT as Field Separator we use Field Data Delimiter. For example, the Account name can be written as “Jahn, Michael” in a file where Comma(,) is Field Separator. Here DoubleQuote(“) protects this field from dividing it into two columns and keep Comma(,) within the data intact. This control allows user to select a data delimiter from dropdown according to his file settings. DoubleQuote is default selection and other options available for Field Data Delimiter are None and SingleQuote(‘). As we are not using data delimiter in our file, we will leave this dropdown in its default state.
First row contains column headings: This check box defines whether the first row in file is header row or not. Generally files are generated with header rows to identify the various columns and data rows start from second row of file but in some situations data can start from first row of file itself. Uncheck this check box if you have no header row defined in your file. Our data file has first header row.
After settings these file options you are ready to go to mapping page.
Now we will click Next which results in screen below.
Mapping of Target fields with Source fields: Mapping of source columns with target attributes is called column mapping. Column mapping defines which source columns data is going to which attribute of selected CRM entity. The Wizard does preliminary column mapping by matching the source column name and target attribute name according to exact string match. Here the user has the option to change the mappings according to the need and intention. Target field dropdowns show all Valid for Create attributes of selected entity and Source Field dropdowns show all the columns present in uploaded data file. User can confirm the correct column mapping by looking at the sample data. The Sample data column shows the first row data for that header.
Disabled Target fields: As you can see in the attached screenshot above that some target field dropdowns are disabled. These are Business Required attributes that cannot be avoided while mapping, and so the user does not have choice for not mapping it. If any of these are missing in source data, then Lead records can’t be created. This behavior is same as creating a single lead record by opening Lead form.
Import to Marketing list: If a user wants to import records and associate these to any existing or new marketing list, select the marketing list in this lookup, otherwise leave blank. In our case we want to add these records to a marketing list named “New Leads” and hence we selected this marketing list in the given field.
Lookup columns: Lookup attributes are reference attributes which refer to other records of same or different entities. If you have mapped any header of your source file with lookup type attribute of CRM entity then GUID of that look up record should be provided. For getting Guid of any record this tool can be used. In our case we are mapping Reference ID header of our file with Customer attribute of Lead which is a lookup type attribute hence we provided Guid of related record in its data which can be seen in Sample data column.
Customer type: Customer is a special lookup type attribute which refers to more than one type of entities. Customer attribute of Lead entity can lookup to Account or Contact records. If a user maps a column of source file to customer attribute of lead then he has to define whether this column data represents Account or Contact. In our case of importing Leads we have multiple data records with many different Customer values but these all values are GUIDs of various Contacts hence I selected Contact in Customer Type dropdown field.
This is the screen after mapping all the fields.
Clicking next on this screen in your CRM instance will lead a user to the Picklist Mapping page.
This is the initial Picklist mapping screen. If you are not importing any Picklist type attribute then this page comes with blank Lists.
Picklist Mapping: Picklists are similar to dropdown lists. They have a fixed set of values and each record has one value from that set. Picklist mapping maps source Picklist values with MSCRM Picklist values. If user has mapped any of his source file header with any Picklist type attribute on previous screen then “Lists column” on this screen shows that mapping. In our case we had mapped “Lead Source” header of our file with “Lead Source” attribute of Lead which is a Picklist type attribute hence this screen is showing us to map the source Picklist values with MSCRM Picklist values. Picklist mappings are created by selecting source row under Mapped Values and selecting correct Picklist under List Values and clicking Map button. To change or unmap any Picklist, select that mapping under Mapped Values and click Unmap button. I mapped Website with Web from List Values dropdown and Friend with other.
If there is no Picklist mapping needed as per selection of column mappings by the user, this page comes with blank Lists section. In this case a user need not do anything here and just click Import.
In case of our source file Lead Source header is mapped to Lead Source attribute, which is a Picklist type attribute, hence this screen appears for only one column mapping in Lists section. This header has two data values under it (Website, Friend) and I mapped them with Web and Other from many available values in the List values dropdown.
The final mapped screen will look like this.
Clicking Import here leads user to the summary screen by submitting the Import job.
This screen gives user import name and tells him the way to look the status of his Bulk Import job.
Click Finish here and you are done with Bulk Import.
To see our bulk import job go to Activities under My Work and filter the activities grid for type Bulk Import and you will see your import there.
This grid had four views associated with it. In-Progress Bulk Imports are the imports which are still in progress.
Double clicking on bulk import record will open the import form.
This form has three tabs under Detail section in left hand side navigation. Leads created shows all the leads created in the system through this import. Information tab shows the general information about this import like, its name, owner, started on, finished on etc. The Failures tab shows the failures if any happen during import and the row wise reason of failure. You can change the erroneous data rows and import them back separately.
Here in the given example I imported only two records but this exercise can be done for thousands of records.
It doesn't address the Company-Contact relationship like my posts below do, but the CRM team has put
This pretty much falls along the lines of the "Import your contacts from Outlook into CRM" document that is pretty easily found.
Where I ran into bumps a month or so ago was bringing in the Companies and then brining in and tying in the actual contacts.
I can tell you that knowing that "Parent Customer" is a GUID field would have shaved a good couple hours off of my time!
Can you please recommend what your best practice would be from importing REAL CRM contacts in the scope of parent companies and child contacts?
Absolutely, the bulk import is a great tool. However, the error handling/reporting leaves a bit to be desired.
Recently we tried to bulk import about 500 contacts, but we received an error that the csv file was corrupt. Normally, turning on Verbose tracing on the server gives you the exact exception that occured. However, now the Verbose logging just repeated the message returned to the user: corrupt file.
Importing the file in segments of 10 contacts at a time reveiled a backslash in a phonenumber field which caused the problem.
So, hopefully in the next version? :)
Hi I was wondering if we can import Activities like phone calls etc for an account. The total Activities run into thousands.
It would be great if someone can help on this
We always use Import Manager from CRM Extensions when we need to import data. It can import to all entities and remove duplets. An extra feature is the possibility to schedule import jobs that gives us the possibility to create integrations to website, ERP systems, etc.
I agree with the problem of the relatioship between company and contact information being maintained. Does anybody know another way of bulk importing and maintaining the relationship.
I have tried several times and have even opened a case number because I keep getting privlages error each time I click the "Import" button after going through all the mapping steps. Do you know if there are privlage issues? We all have blanket as installed roles. I have all the roles available assigned to me, even sys admin. Our assigned tech seems lost and the trace didn't seem to help. Just thought I would ask one more person in case it is an easy fix.
Is there any official (and useful!) Bulk Import documentation?
The bulk import utility is poorly implemented. Whilst from a technical point of view there are some clever features it's almost impossible to use useless you have a technical background. Our marketing team can manage to import data into Access and Excel. However CRM's fussy CSV handling, lack of instant feedback and the cryptic errors make the import feature unusable by the very people who would use it most (e.g. Marketing staff). Fortunately as an IT company we have staff on hand to resolve this, I suspect many SME's will not.
I hope this functionality is improved in V4 as its the one area of the product which I think is really weak.
I have do like that but in Activities, all the import process in processing, when will it finished?
You might want to reset your Workflow Engine and set the login to Local Network login.
Terry, check this : http://www.microsoft.com/dynamics/crm/using/troubleshooting/tsbulkimport.mspx
I believe there will be hardly anyone who will disagree to the fact that the performance of marketing
What a great this comment so is very nice I like this site for me wish he all the best.