Export (0) Print
Expand All

Using Complex Data Mapping in Data Import for Microsoft Dynamics CRM 4.0

Inna Agranov
Microsoft Corporation

October 2008

Summary

Learn how to create a complex data mapping and import data by using Microsoft Dynamics CRM 4.0 Web Services.

Download the Visual Studio 2005 Visual C# code sample for this article: ComplexMapping. The Readme.doc that is included with the sample code contains information about how to set up, build, run and test the sample application.

Applies To

Microsoft Dynamics CRM 4.0

Microsoft Visual Studio 2005

Introduction

Data import provides a way to upload data from various customer relationship management systems and data sources into Microsoft Dynamics CRM. You can import data into standard and customized attributes of most entity types, including custom entities. During data import you can import new data or update existing data in Microsoft Dynamics CRM. The data source files that are used in data import must be formatted as comma-separated values (CSV) files. The use of CSV files enables the transfer of data between database systems that use different formats.

Creating Data Maps for Data Import

Data maps are used to map source data that is contained in CSV source files to Microsoft Dynamics CRM entity attribute types. You have to map a column in the source file to an appropriate Microsoft Dynamics CRM entity attribute. The data in the unmapped columns is not imported during the data import operation.

The data map contains the mappings listed in the following table.

 

Mapping Type

Description

Column

Maps a column in a source file to a Microsoft Dynamics CRM entity attribute. Use the column mapping.

List value

Maps a list value in a source file to a list value in Microsoft Dynamics CRM. Use the picklist mapping (value list mapping).

Lookup

Maps a lookup value in a source file to a Microsoft Dynamics CRM entity attribute of lookup type.

Use the lookup mapping entity to implement lookup mapping.

Data Maps

The data map is represented by the import map (data map) entity. You can create a new map by using the Create method or update an existing map by using the Update method. The map has a unique name that is contained in the importmap.name property. You can specify the name of the migration source for which this data map is created by using the importmap.source property.

Column Mapping

You have to provide column mapping for every column in the source file. You can use 1:1 (one-to-one) or 1:N (one-to-many) relationships between source and target attributes. For example, you can map account address information to the billing and shipping addresses in an order.

List Value Mapping

If a value specified the source file column is a list value, such as picklist, status, state, or Boolean, you have to provide a list value mapping in addition to a column mapping. For example, map the "bill" and "ship" list values in the source file to the ship and bill values of picklist type inside Microsoft Dynamics CRM.

The asynchronous transformation job processes all available mappings. It finds the referenced entity instances and updates the parse table with the entity instance unique identifiers.

Lookup Mapping

If the value specified in an import or data migration source file references an entity, you have to provide a lookup mapping for this value. A lookup mapping specifies where to search for the referenced entities. To search inside the source file, set the lookupmapping.lookupsourcecode property to the LookupSourceType.Source value of the LookupSourceType enumeration. To search inside Microsoft Dynamics CRM, set the lookupmapping.lookupsourcecode property to the LookupSourceType.System value of the LookupSourceType enumeration. You can search in the source file and in Microsoft Dynamics CRM by providing two lookup mappings.

The asynchronous transformation job processes all available mappings. It finds the referenced entity instances and updates the parse table with the entity instance unique identifiers.

Importing data

Typically, to implement data import you have to follow these steps:

  • Create CSV formatted source files that contain the source data.

  • Create the data maps, or use the existing data maps. This step is not required if you use auto-mapping. You can use auto-mapping if the column names in the source file are identical to the display names of the Microsoft Dynamics CRM entity attributes.

  • Create the import files and read the content of the CSV source files into the associated import files.

  • Parse the import files.

  • Upload transformed data into the target Microsoft Dynamics CRM server.

Each source file contains data for one entity type, such as an account, a lead, or a competitor. During import you can process one source file at the time

Preparing Source Files for Import

The data source files that are used in import and data migration must be formatted as CSV files. The use of CSV files enables the transfer of data between database systems that use different formats. You can easily create CSV-formatted files by using Microsoft Office Excel.

The field values in the source file can be separated by commas, tabs, or other characters that are defined in the importfile.fielddelimitercode property.

noteNote
Do not use non-printable characters, such as newline (\n) or return (\r) characters, as delimiters for the field values.

Each source file must contain data of one entity type, such as accounts, leads, or competitors.

The first row in the source file should contain column headings. If you do not include the headings, use the importfile.isfirstrowheader property to specify that the first row represents actual data. In this case, default column headings are created with the names Col1, Col2, and so on.

For more information about how to create and prepare CSV source files for import and data migration, see the Microsoft Dynamics CRM online Help.

Configuring Data Import

The configuration information that is required for running data import is contained in the import (data import) and import file entities.

The import entity contains status and ownership information for an import job. It specifies the operation mode, such as import or migration, and whether the data is created or updated during import.

Use the following import data (import) entity properties to configure data import:

  • Set the import.modecode property to ImportModeCode.Create or ImportModeCode.Update values of the ImportModeCode enumeration to create or update data during import.

  • Set the import.isimport property to true to indicate that the operation is data import.

The import file entity represents a source file that is used in data import. It contains the name of the parse table that is associated with the import file. It also contains the unique identifiers of associated import map (data map) and import (data import).

Set the following import file entity properties to configure data import:

Running Data Import

Parsing, transforming, and uploading of data are done by the asynchronous jobs that run in the background. The asynchronous jobs have to run in this particular order:

  1. Parse

  2. Transform

  3. Upload

A new job cannot start before the previous job has finished running successfully.

Data import runs directly on the Microsoft Dynamics CRM server.

Parsing Source Data

Parsing of the source data includes parsing of all import files associated with a particular import (data import).

Parsed data is stored in the temporary parse tables that are created for every import file. The name of the parse table is stored in the importfile.parsedtablename property. Save parsed data in the parse table by using the ParseImport message. Retrieve data from the parse table by using the GetDistinctValuesImportFile message and the RetrieveParsedDataImportFile message. The source file column headings are specified in the importfile.headerrow property. If the source file does not include a first row that contains the column headings, this property specifies the system-generated default column headings.

Use the GetDistinctValuesImportFile message and the RetrieveParsedDataImportFile message only after you create a parse table by using the ParseImport message.

Do not use the GetDistinctValuesImportFile message and the RetrieveParsedDataImportFile message after you use the ImportRecordsImport message. You cannot access the parse table after the import job submitted by the ImportRecordsImport message has finished running.

Transforming Parsed Data

During transformation you change parsed data by applying all available data mappings and transformations that are associated with a particular import (data import) to the data.

Use the TransformImport message to submit an asynchronous job to transform the parsed data. Pass a unique identifier of the associated import (data import) in the TransformImportRequest.ImportId property of the request. A unique identifier of the asynchronous job that runs in the background and performs the transformation is returned in the TransformImportResponse.AsyncOperationIdproperty of the message response.

Uploading Transformed Data to the Target Server

After you successfully complete the transformation, the data is ready to be uploaded into the Microsoft Dynamics CRM server.

Use the ImportRecordsImport message to submit an asynchronous job to upload the transformed data into Microsoft Dynamics CRM. The unique identifier of the associated import (data import) must be specified in the ImportRecordsImportRequest.ImportId property of the request. A unique identifier of the asynchronous job that runs in the background and uploads the data into Microsoft Dynamics CRM is returned in the ImportRecordsImportResponse.AsyncOperationId property of the message response. All import files that are associated with the specified import (data import) are imported

Implementation

Learn how to create a data map that maps columns in the source file to the attributes of type lookup and type picklist, and how to run data import.

Source File

The import_accounts.csv source file consists of five records. Each record contains a name of the account, a parent account name and an address type, such as "bill" or "ship"

The first column in the source file is a text type, the second column is a lookup type, and the third column is a picklist type.

Creating a Data Map

Create a data map by using the import map (data map) entity. Use the importMap.source property to specify the source file that contains the data you are importing.

// Create an import map.
importmap importMap = new importmap();
importMap.name = "Import Map " + DateTime.Now.Ticks.ToString();
importMap.source = "import_accounts.csv";
importMap.description = "Description of data being imported";
Guid importMapId = service.Create(importMap);

Creating Column Mappings

Create column mappings for all columns in the source file.

Column One Mapping

Create a column mapping for a "source_name" column that is a text type. It contains a name of an account

// Create a column mapping for a "text" type field.
columnmapping colMapping1 = new columnmapping();

// Set the source properties. "Account_1" is an arbitrary name of an import file.
colMapping1.sourceattributename = "src_name";
colMapping1.sourceentityname = "Account_1";

// Set the target properties.
colMapping1.targetattributename = "name";
colMapping1.targetentityname = EntityName.account.ToString();

// Associate the column mapping with the data map.
colMapping1.importmapid = new Lookup();
colMapping1.importmapid.type = EntityName.importmap.ToString();
colMapping1.importmapid.Value = importMapId;

// Process the column. To skip a column, use "Ignore". 
colMapping1.processcode = new Picklist();
colMapping1.processcode.Value = ImportProcessCode.Process;

// Create the column mapping entity instance in Microsoft Dynamics CRM. 
Guid colMappingId1 = service.Create(colMapping1);

Column Two Mapping

Create the mappings for a "src_parent" column that is a lookup type. The column contains a name of a parent account. For this column, create a column mapping and a lookup mapping, and associate the lookup mapping with the column mapping. Also, create a lookup mapping for a current account (an account that is specified in column one) and associate it with the column mapping. If do not create a lookup mapping for a current account, the records that use this account as a parent account will not be imported.

// Create a column mapping for a "lookup" type field.
columnmapping colMapping2 = new columnmapping();

// Set the source properties. "Account_1" is an arbitrary name of an import file.
colMapping2.sourceattributename = "src_parent";
colMapping2.sourceentityname = "Account_1";

// Set the target properties.
colMapping2.targetattributename = "parentaccountid";
colMapping2.targetentityname = EntityName.account.ToString();

// Associate the column mapping with the data map.
colMapping2.importmapid = new Lookup();
colMapping2.importmapid.type = EntityName.importmap.ToString();
colMapping2.importmapid.Value = importMapId;

// Specify to process the column. To skip a column, use "Ignore".
colMapping2.processcode = new Picklist();
colMapping2.processcode.Value = ImportProcessCode.Process;

// Create the column mapping entity instance in Microsoft Dynamics CRM.
Guid colMappingId2 = service.Create(colMapping2);

// Create the lookup mapping for the parent record and a current record.

// Create a lookup mapping for the parent record.
lookupmapping parentLookupMapping = new lookupmapping();

// Associate the lookup mapping with the parent column mapping.
parentLookupMapping.columnmappingid = new Lookup();
parentLookupMapping.columnmappingid.type = EntityName.columnmapping.ToString();
parentLookupMapping.columnmappingid.Value = colMappingId2;

// Process the column. To skip a column, use "Ignore".
parentLookupMapping.processcode = new Picklist();
parentLookupMapping.processcode.Value = ImportProcessCode.Process;

// Set the parent lookup mapping properties.
parentLookupMapping.lookupentityname = EntityName.account.ToString();
parentLookupMapping.lookupattributename = "name";
parentLookupMapping.lookupsourcecode = new Picklist();
parentLookupMapping.lookupsourcecode.Value = LookUpSourceType.System;

// Create the lookup mapping entity instance for a parent record in Microsoft Dynamics CRM.
Guid parentLookupMappingId = service.Create(parentLookupMapping);

// Create a lookup mapping for the current record. 
// If you do not create a lookup mapping for the current record, the 
// records that use this record as a parent will not be imported.
lookupmapping currentLookupMapping = new lookupmapping();

// Associate the lookup mapping with the parent column mapping.
currentLookupMapping.columnmappingid = new Lookup();
currentLookupMapping.columnmappingid.type = EntityName.columnmapping.ToString();
currentLookupMapping.columnmappingid.Value = colMappingId2;

// Specify to process the column. To skip a column, use "Ignore".
currentLookupMapping.processcode = new Picklist();
currentLookupMapping.processcode.Value = ImportProcessCode.Process;

// Set the current lookup mapping properties.
// "Account_1" is an arbitrary name of an import file
currentLookupMapping.lookupattributename = "src_name";
currentLookupMapping.lookupentityname = "Account_1";
currentLookupMapping.lookupsourcecode = new Picklist();
currentLookupMapping.lookupsourcecode.Value = LookUpSourceType.Source;

// Create the lookup mapping entity instance for a current record in Microsoft Dynamics CRM.
Guid currentLookupMappingId = service.Create(currentLookupMapping);

Column Three Mapping

Create the mappings for a "src_addresstype" column that is a picklist type. This column contains an address type, such as "bill" or "ship". For this column, create a column mapping and a picklist mapping, and associate the picklist mapping with the column mapping. You have to create a picklist mapping for every address type specified in the source file and associate it with the column mapping.

// Create a column mapping for a "picklist" type field.
columnmapping colMapping3 = new columnmapping();

// Set the source properties. "Account_1" is an arbitrary name of an import file.
colMapping3.sourceattributename = "src_addresstype";
colMapping3.sourceentityname = "Account_1";

// Set the target properties.
colMapping3.targetattributename = "address1_addresstypecode";
colMapping3.targetentityname = EntityName.account.ToString();

// Associate the column mapping with the parent data map.
colMapping3.importmapid = new Lookup();
colMapping3.importmapid.type = EntityName.importmap.ToString();
colMapping3.importmapid.Value = importMapId;

// Specify to process the column. To skip a column, use "Ignore".
colMapping3.processcode = new Picklist();
colMapping3.processcode.Value = ImportProcessCode.Process;

// Create the column mapping entity instance in Microsoft Dynamics CRM.
Guid colMappingId3 = service.Create(colMapping3);

// Create a picklist mapping and set the properties.
picklistmapping picklistMapping1 = new picklistmapping();
picklistMapping1.sourcevalue = "bill";
picklistMapping1.targetvalue = new CrmNumber();
picklistMapping1.targetvalue.Value = 1;

// Associate the picklist mapping with the parent column mapping.
picklistMapping1.columnmappingid = new Lookup();
picklistMapping1.columnmappingid.type = EntityName.columnmapping.ToString();
picklistMapping1.columnmappingid.Value = colMappingId3;

// Process the column. To skip a column, use "Ignore".
picklistMapping1.processcode = new Picklist();
picklistMapping1.processcode.Value = ImportProcessCode.Process;

// Create the picklist mapping entity instance in Microsoft Dynamics CRM.
Guid picklistMappingId1 = service.Create(picklistMapping1);

// Create a picklist mapping for every address type specified in the source file.
picklistmapping picklistMapping2 = new picklistmapping();
picklistMapping2.sourcevalue = "ship";
picklistMapping2.targetvalue = new CrmNumber();
picklistMapping2.targetvalue.Value = 2;

// Associate the picklist mapping with the parent column mapping.
picklistMapping2.columnmappingid = new Lookup();
picklistMapping2.columnmappingid.type = EntityName.columnmapping.ToString();
picklistMapping2.columnmappingid.Value = colMappingId3;

// Process the column. To skip a column, use "Ignore".
picklistMapping2.processcode = new Picklist();
picklistMapping2.processcode.Value = ImportProcessCode.Process;

// Create the picklist mapping entity instance in Microsoft Dynamics CRM.
Guid picklistMappingId2 = service.Create(picklistMapping2);

Importing data

The following code shows how to create, configure and run an import.

// Create an import (data import) entity instance.
import import = new import();
import.name = "Importing data";

// Distinguish between an import and an update.
import.isimport = new CrmBoolean();
import.isimport.Value = true;
import.modecode = new Picklist();
import.modecode.Value = ImportModeCode.Create; 

// Create the import (data import) entity instance. 
// in Microsoft Dynamics CRM
Guid importId = service.Create(import);

// Create a logical source file of the data being imported.
importfile importFile = new importfile();
importFile.name = "Account record import.";
importFile.source = "import_accounts.csv";
importFile.sourceentityname = "Account_1";
importFile.targetentityname = EntityName.account.ToString();

// Read contents from disk.
importFile.content = ReadCsvFile("import_accounts.csv");

// Configure delimiters.
importFile.datadelimitercode = new Picklist();
importFile.datadelimitercode.Value = ImportDataDelimiter.DoubleQuote;
importFile.fielddelimitercode = new Picklist();
importFile.fielddelimitercode.Value = ImportFieldDelimiter.Comma;

// Do not allow duplicate records to import.
importFile.enableduplicatedetection = new CrmBoolean();
importFile.enableduplicatedetection.Value = false;

// Relate the import file to the parent import (data import).
importFile.importid = new Lookup();
importFile.importid.type = EntityName.import.ToString();
importFile.importid.Value = importId;

// Relate this import file to the parent data map.
importFile.importmapid = new Lookup();
importFile.importmapid.type = EntityName.importmap.ToString();
importFile.importmapid.Value = importMapId;

// Do not use the first row during import.
importFile.isfirstrowheader = new CrmBoolean();
importFile.isfirstrowheader.Value = true;

// Specify to process the column.
importFile.processcode = new Picklist();
importFile.processcode.Value = ImportProcessCode.Process;

// Specify the current user as the record owner.
WhoAmIRequest systemUserRequest = new WhoAmIRequest();
WhoAmIResponse systemUserReponse = (WhoAmIResponse)service.Execute(systemUserRequest);

// Specify the owner ID.
importFile.recordsownerid = new Lookup();
importFile.recordsownerid.type = EntityName.systemuser.ToString();
importFile.recordsownerid.Value = systemUserReponse.UserId;

// Create the import file.
Guid importFileId = service.Create(importFile);

// Retrieve the header columns used in the import file.
GetHeaderColumnsImportFileRequest headerColumnsRequest = new GetHeaderColumnsImportFileRequest();
headerColumnsRequest.ImportFileId = importFileId;
GetHeaderColumnsImportFileResponse headerColumnsResponse = (GetHeaderColumnsImportFileResponse)service.Execute(headerColumnsRequest);

// Output the header columns.
int columnNum = 1;
foreach(string headerName in headerColumnsResponse.Columns)
{
Console.WriteLine("Column[" + columnNum.ToString() + "] = " + headerName);
columnNum++;
}

// Parse the import file.
ParseImportRequest parseImportRequest = new ParseImportRequest();
parseImportRequest.ImportId = importId;
ParseImportResponse parseImportResponse = (ParseImportResponse)service.Execute(parseImportRequest);
Console.WriteLine("Waiting for Parse async job to complete");
WaitForAsyncJobCompletion(service, parseImportResponse.AsyncOperationId);
ReportErrors(service, importFileId);

// Retrieve the first 2 distinct values for column 1 from the parse table.
// You must create the parse table by using the ParseImport message 
// before using the GetDistinctValuesImportFile message.
// The parse table is not accessible after you use 
// the ImportRecordsImport message.
GetDistinctValuesImportFileRequest distinctValuesRequest = new GetDistinctValuesImportFileRequest();
distinctValuesRequest.columnNumber = 1;
distinctValuesRequest.ImportFileId = importFileId;
distinctValuesRequest.pageNumber = 1;
distinctValuesRequest.recordsPerPage = 2;
GetDistinctValuesImportFileResponse distinctValuesResponse = (GetDistinctValuesImportFileResponse)service.Execute(distinctValuesRequest);

// Output the distinct values.  In this case, the values are (column 1, row 1) and (column 1, row 2).
int cellNum = 1;
foreach(string cellValue in distinctValuesResponse.Values)
{
Console.WriteLine("(1, " + cellNum.ToString() + "): " + cellValue);
Console.WriteLine(cellValue);
cellNum++;
}

// Retrieve data from the parse table.
// You must create the parse table by using the ParseImport message 
// before using the RetrieveParsedDataImportFile message.
// The parse table is not accessible after you 
// use the ImportRecordsImport message.
RetrieveParsedDataImportFileRequest parsedDataRequest = new RetrieveParsedDataImportFileRequest();
parsedDataRequest.ImportFileId = importFileId;
parsedDataRequest.PagingInfo = new PagingInfo();

// Specify the number of entity instances returned per page.
parsedDataRequest.PagingInfo.Count = 2;

// Specify the number of pages returned from the query.
parsedDataRequest.PagingInfo.PageNumber = 1;

// Specify a total number of entity instances returned.
parsedDataRequest.PagingInfo.PagingCookie = "1";

RetrieveParsedDataImportFileResponse parsedDataResponse = (RetrieveParsedDataImportFileResponse)service.Execute(parsedDataRequest);

// Output the first two rows retrieved.
int rowCount = 1;
foreach(string[] rows in parsedDataResponse.Values)
{
int colCount = 1;
foreach(string column in rows)
{
Console.WriteLine("(" + rowCount.ToString() + "," + colCount.ToString() + ") = " + column);
colCount++;
}
rowCount++;
}

// Run the transform.
TransformImportRequest transformImportRequest = new TransformImportRequest();
transformImportRequest.ImportId = importId;
TransformImportResponse transformImportResponse = (TransformImportResponse)service.Execute(transformImportRequest);
Console.WriteLine("Waiting for Transform async job to complete");
WaitForAsyncJobCompletion(service, transformImportResponse.AsyncOperationId);
ReportErrors(service, importFileId);

// Import the records.
ImportRecordsImportRequest importRecordsImportRequest = new ImportRecordsImportRequest();
importRecordsImportRequest.ImportId = importId;
ImportRecordsImportResponse importRecordsImportResponse = (ImportRecordsImportResponse)service.Execute(importRecordsImportRequest);
Console.WriteLine("Waiting for ImportRecords async job to complete");
WaitForAsyncJobCompletion(service, importRecordsImportResponse.AsyncOperationId);
ReportErrors(service, importFileId);

Additional InformationAdditional InformationAdditional Information

Download the Microsoft Dynamics CRM 4.0 Software Development Kit (SDK) from the MSDN Developer Center.

For more information about data import, see Data Migration and Import.

Send Us Your Feedback about this Article

We appreciate hearing from you. To send your feedback, click the following link and type your comments in the message body.

noteNote
The subject-line information is used to route your feedback. If you remove or modify the subject line we may be unable to process your feedback.

Send Feedback

 

 

Community Additions

ADD
Show:
© 2014 Microsoft