CRM Data Import Tool
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:
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.
On selecting the desired view and clicking Show Records, all the MSCRM records corresponding to the selected view will be available in the Grid.
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.
Monitor the progress of the import job in MSCRM by viewing the Workplace->Imports section.
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.
Steps for Importing the CSV file for update in CRM Web UI:
Launch MSCRM and On the Tools menu, click Import Data.
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.
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
