The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

CRM Data Import Tool

CRM Data Import Tool

  • Comments 54

In Microsoft Dynamics CRM 4.0 it is  possible to export the MSCRM data, modify and re-import data using a tool called MSCRM Import. There are two ways to use this tool. You can edit the records of MSCRM views in the MSCRM Update tool and submit them back to MSCRM. Or you can export the data of any MSCRM view to CSV file, which can be later imported back into MSCRM to update the records. The complete code for the tool is provided here for the avid developers who want to develop solutions currently beyond the scope of this tool. The sample code included uses standard MSCRM SDK calls to achieve this functionality.

The MSCRM Import tool can be downloaded from http://www.codeplex.com/MSCRMimport. Follow the instructions in Readme.Txt for installation.

After extracting the files, launch Export.exe in the Release directory. Provide the MSCRM credential using this screen:

clip_image002

Select the entity from the entity drop down box; this will populate system views and user saved views in the views drop down box for the selected entity.

clip_image004

On selecting the desired view and clicking Show Records, all the MSCRM records corresponding to the selected view will be available in the Grid.

clip_image006

Update Records in MSCRM: The data shown in the grid is editable. Update the records in the grid and then click Update Records in MSCRM button. An import job is submitted to MSCRM. Only the modified records will be updated in MSCRM. The modified records are highlighted with Red background as shown in figure below.

clip_image008

Monitor the progress of the import job in MSCRM by viewing the Workplace->Imports section.

clip_image010

Export Records to CSV: Data shown in the Grid can be saved in CSV format by clicking on Export Records to CSV. Select field delimiter (Character used to separate columns of data.) and data delimiter (The character used to surround data that includes the field delimiter. For example, if the field delimiter is a comma, and the data delimiter is quotation marks, one column in a file could contain "Redmond, WA" and still be treated as a single column).

The records in saved CSV file can be edited offline and can be re-imported using the MSCRM Import functionality. MSCRM Import tool will automatically detect that the records are valid for update of existing records.

clip_image012

Steps for Importing the CSV file for update in CRM Web UI:

Launch MSCRM and On the Tools menu, click Import Data.

clip_image014

Specify the CSV file that was exported above. Make sure that on the Select the Record Type and Map screen, the Enrich data by updating records rather than creating new records check box is selected (by default it is selected). If you clear the Enrich data by updating records rather than creating new records check box, the records will not be updated.

Note: A record will not be updated if it has been changed in Microsoft Dynamics CRM after it was exported.

clip_image016

In the Map Source Data to Microsoft Dynamics CRM section you will always see at least one Ignored column warning for the Modified On column. This column is required for enriching data, but is not imported, so ignore the warning.

Click Next, and then click Next again. Rename the import job if needed, change the notification options if needed, and then click Import.

Code Flow Details

We begin with connecting to CRM service and downloading all the entities and its metadata.

//Metadata Service Object

crmMetadataService = new MetadataService();

crmMetadataService.Url = m_ServerURL + "/mscrmservices/2007/MetadataService.asmx";

crmMetadataService.Credentials = new System.Net.NetworkCredential(m_User, m_Pass, m_Domain);

Microsoft.Crm.Sdk.CrmAuthenticationToken token = new Microsoft.Crm.Sdk.CrmAuthenticationToken();

//Authentication type to AD for On-Premise users

token.AuthenticationType = 0 ;

//m_orgname – Organization name of the user

token.OrganizationName = m_orgname;

crmMetadataService.CrmAuthenticationTokenValue = token;

// crmMetadataService.UnsafeAuthenticatedConnectionSharing = true;

//Crm Service Object

_CrmService = new CrmService();

//m_serverURL like http://<servername>

_CrmService.Url = m_ServerURL + "/mscrmservices/2007/CrmService.asmx";

_CrmService.Credentials = new System.Net.NetworkCredential(m_User, m_Pass, m_Domain);

_CrmService.CrmAuthenticationTokenValue = token;

//_CrmService.UseDefaultCredentials = true;

// _CrmService.UnsafeAuthenticatedConnectionSharing = true;

WhoAmIRequest userRequest = new WhoAmIRequest();

_CrmService.Execute(userRequest);

// Retrieving all the entities

RetrieveAllEntitiesRequest crmMetadataRequest = new RetrieveAllEntitiesRequest();

crmMetadataRequest.MetadataItems = MetadataItems.IncludeAttributes;

RetrieveAllEntitiesResponse crmMetadataResponse = (RetrieveAllEntitiesResponse)crmMetadataService.Execute((MetadataServiceRequest)crmMetadataRequest);

Once we have the list of all the entities, we load all the system and user views for each entities that are valid for import.

/// <summary>

/// This method is used to retrive all the system default views associated with given entity

/// </summary>

public BusinessEntityCollection SystemViewCollection(int objectTypeCode)

{

QueryExpression systemQuery = new QueryExpression();

systemQuery.EntityName = EntityName.savedquery.ToString();

ColumnSet systemQueryCols = new ColumnSet();

systemQueryCols.AddColumns(new string[] { "name", "savedqueryid", "fetchxml", "layoutxml" });

systemQuery.ColumnSet = systemQueryCols;

ConditionExpression systemQueryCondition = new ConditionExpression();

systemQueryCondition.AttributeName = "returnedtypecode";

systemQueryCondition.Operator = ConditionOperator.Equal;

systemQueryCondition.Values = new Object[] { objectTypeCode };

ConditionExpression systemQueryCondition1 = new ConditionExpression();

systemQueryCondition1.AttributeName = "querytype";

systemQueryCondition1.Operator = ConditionOperator.Equal;

systemQueryCondition1.Values = new object[] { 0 };

ConditionExpression systemQueryCondition2 = new ConditionExpression();

systemQueryCondition2.AttributeName = "fetchxml";

systemQueryCondition2.Operator = ConditionOperator.NotNull;

FilterExpression feSystemQuery = new FilterExpression();

feSystemQuery.FilterOperator = LogicalOperator.And;

feSystemQuery.Conditions.Add(systemQueryCondition);

feSystemQuery.Conditions.Add(systemQueryCondition1);

feSystemQuery.Conditions.Add(systemQueryCondition2);

systemQuery.Criteria = feSystemQuery;

BusinessEntityCollection responseSystemquery = _CrmService.RetrieveMultiple(systemQuery);

return responseSystemquery;

}

Similarly we fetch the user views associated with any entity in method UserViewCollection.

The System and User views of the selected entities are shown in the selection box. When user selects a view and clicks the “Show Records” button, we retrieve all the columns of that particular view and show to the user in the data grid.

resultTable = Exh.ExecuteQuery(lstSavedQuerySelectedItem.SavedQueryXml, colsName);

In addition to the columns defined in the saved view, we also get the primary key and ‘last modified’ fields for the records. Having these two columns is necessary for preparing the data that we can re-import to update the records.

The user can modify the records in the data grid and then click the button “Update Records in MSCRM”. This internally constructs a CSV file with modified records contents and submits an import job to the MSCRM service.

changedCSV = Exh.ExportCsvString(cmbfield.SelectedItem.ToString().Trim(), cmbdata.SelectedItem.ToString().Trim(), changedt);

Exh.importUpdatetoCRM(changedCSV, cmbdata.SelectedItem.ToString().Trim(), cmbfield.SelectedItem.ToString().Trim());

To submit this CSV to import job following SDK calls needs to be made:

Obtain an Importid from CRM System:

import imp = new import();

importid = _CrmService.Create(imp);

l1.Value = importid;

Create an import file Business Entity with the obtained importId:

importfile impf = new importfile();

impf.importid = l1;

importfileid = _CrmService.Create(impf);

With this importid and first create ParseImportRequest:

ParseImportRequest request = new ParseImportRequest();

request.ImportId = importId;

ParseImportResponse response = null;

response = (ParseImportResponse)_CrmService.Execute(request);

Then create TransformImportRequest:

TransformImportRequest tranreq = new TransformImportRequest();

tranreq.ImportId = importId;

TransformImportResponse tranresp = null;

tranresp = (TransformImportResponse)_CrmService.Execute(tranreq);

Finally create ImportRecordsImportRequest:

ImportRecordsImportRequest impreq = new ImportRecordsImportRequest();

impreq.ImportId = importId;

ImportRecordsImportResponse impresp = null

impresp = (ImportRecordsImportResponse)_CrmService.Execute(impreq);

This submits the import job to MSCRM. You can go to the MSCRM Workplace->Imports section and monitor the progress of the job.

Similarly, if you export the records to CSV, you can create a CSV file and save the data grid view into the CSV file. It is important to have Primary Key as the first column. If this is present, the CRM import UI automatically recognizes that the given file is for updating the records. During import of CSV file, the user needs to make sure that on the Select the Record Type and Map screen, the Enrich data by updating records rather than creating new records check box is selected (it is selected if you will use the CSV that you get from this tool ). This is due to the presence of primary Key column of the records as the first column in the CSV file. If this checkbox is not selected, the records will not be updated.

The main advantage of the tool is that if the data is modified and updated in MSCRM with this tool (by clicking Update Records in MSCRM button), only the records that have been modified are submitted. This avoids unnecessary triggering of workflows related to unmodified records in the view. On the other hand if you export to the CSV file, modify few records and then import back using MSCRM import interface, it even imports the rows that are not modified. This is because there is no way for MSCRM to know which records have been changed. This triggers unnecessary Workflow associated with these records.

This gives you a complete flow of how you can export the records from MSCRM, modify them and re-import them back for update.

Authors:

Veeran Bansal

Adithya Vishwanath

Arun Kumar

  • PingBack from http://www.availabledomain.co.cc/crm-data-import-tool

  • Interesting: blogs.msdn.com

  • It is very informative. Thanks for the blog.

  • For CRM 4.0, our Data Management Team introduced several APIs that allow you to bulk edit records in

  • For CRM 4.0, our Data Management Team introduced several APIs that allow you to bulk edit records in

  • Some of you may have been frustrated that before the launch of Microsoft Dynamics CRM 4.0, there were

  • Some of you may have been frustrated that before the launch of Microsoft Dynamics CRM 4.0, there were

  • Can you use the tool to update a lookup field? ie - a contact's parent account, in the grid view?

  • Yes you can as long as the new parent account already exists. The tool just submits the update to CRM. The success and failure is handled by CRM, the tool has no role in it.

    >Can you use the tool to update a lookup field? ie - a contact's parent account, in the grid view?

  • Hi

    I am trying to import data into CRM 4.0 using data management tool.

    1. I have created a new mapping.

    2. The data are correctely mapped into CRM

    3. I tried importing data using import data from tools.

    4. I am able to complete the wizard process correctly

    5. I do see the job created under the imports.

    6. I do see the status "InProcess"

    7. It is been 2 days already, I do not see the data been imported into my CRM database.

    Please what could be wrong.

    How can I get them instantly imported into my entity.

    Please advice

    patric

    patric1776@hotmail.com

  • Hello,

    I have the same problem as patric:

    1. I have created a new mapping.

    2. The data are correctely mapped into CRM

    3. I tried importing data using import data from tools.

    4. I am able to complete the wizard process correctly

    5. I do see the job created under the imports.

    6. I do see the status "InProcess"

    7. It is been 2 days already, I do not see the data been imported into my CRM database.

    Just that I noticed that there is an error in the SQL Agent, the user (which is an Administrator) cannot login and so the job fails.

    How could this problem be solved?

  • The tool has been developed and uploaded to http://www.codeplex.com/Wiki/View.aspx?ProjectName=MSCRMimport

  • The tool has been developed and uploaded to http://www.codeplex.com/Wiki/View.aspx?ProjectName=MSCRMimport

  • Let me guess, either you are looking to learn ‘how to import data’ or you are stuck with some issues

  • Hi all,

    Does anyone know why this the Codeplex website is no longer available?

    Is there somewhere else I can get this tool?

    Thanks!

    Harry

Page 1 of 4 (54 items) 1234
Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post