Import of complex Excel files into Microsoft CRM
It is a common task to import data into CRM from another system on the condition that the export format is limited. How to use CRM Migration Tool (powered by Wave.Integrate) when complex Excel files should be imported into Microsoft CRM?
It is often needed to import data into CRM from another system on the condition that the export format of the system is limited. In this article we will describe how to use CRM Migration Tool (powered by Wave.Integrate platform) when complex EXCEL files should be imported into MS CRM. This example is based on the data import that we did for one of our clients. The client came to us to get help with importing Opportunities into MS CRM 2011 from another system. The data were provided in .xls file format. At the same time the client’s CRM was heavily customized: Opportunities had a lot of custom fields and “Many-to-Many” relationships to a custom entity and Users, The data of the provided .xls file were displayed on a single sheet, the related records were in one column and were separated by commas. Unfortunately, the standard CRM import is quite limited in this situation. It is impossible to create a new record for lookup, if it is not found in CRM, as well as it is impossible to create “Many-To-Many” relationship. So we decided to use our special product CRM Migration Tool (powered by Wave.Integrate platform).
Preparing the data
Data format: one line from the provided .xls file.
|
1 |
Port of Oakland |
|
2 |
Conley |
|
3 |
14.04.2013 |
|
4 |
05.04.13 |
|
5 |
Hard Copy |
|
6 |
Susan Brice |
|
7 |
Meryl Macklin - Com Lit, Steve Richardson - ENR, Susan Brice - ENV, Joseph McCoy - RE |
|
8 |
Com Lit, ENR, ENV, RE |
|
9 |
Diversity, Conlficts, Probono |
|
10 |
Hourly Rate |
|
11 |
Legal services in 14 areas; bidding on four; highlighting capabilties in others |
|
12 |
40 Hrs - Conley, others |
Columns: 1 - lookup on the Account and Opportunity; 2 - The owner of the record; 6 - lookup on the User; 7 – “Many-To-Many” relationship with Users; 8 – “Many-To-Many” relationship with a custom entity; 11 - Notes; 3,4,5,9,10,12 - Standard fields.
Now we can see the first difficulty: the names in columns 2, 6, 7 are not standardized and do not match the users in the CRM. So, the following operation was performed: the data of these three columns were copied into a single column in Excel (column 7 was pretreated by function “Text to Columns” with the help of comma-delimited). Thus, in the same column we've got all kinds of users’ spellings. The next step was to export the users’ GUID from CRM and to compare it with the obtained data. How it will be used in the script, we will see further. Below is an example of the resulting row of the data:
|
From List |
Currently in CRM |
SystemUserId Production system |
SystemUserId Development |
|
Conley |
Steve Conley |
D99435EC-7E92-E311-AC0A-005056922C86 |
E8508251-0F93-E311-82ED-0050569270F6
|
As it often happens, the customer had two environments and, consequently, two CRM organizations. So, the import had to be produced in both organizations. Fortunately, CRM Migration Tool can be used in different organizations and countless number of times. It was only necessary to change the matching of users’ spellings to the GUID’s from the definite CRM organization.
After this the data without any changes have been imported into SQL with the help of the standard capabilities of SQL Management Studio.
Beginning of the script, fields mapping, the structure
The first step is to create the file Usermapping.xml. Below is the beginning of the script with the comment:
<?xml version="1.0" encoding="UTF-8"?>
<operations> <!-- The main operator, instructs Wave.Integrate that script began -->
<log>UsersMapping load</log> <!-- log operator displays information in output window -->
<!-- Fill the array UserMapping with required attributes,
each line add an element to array with two attributes: name and guid -->
<set var="UserMapping[]"><attr name="name">Adam Sher</attr><attr name="guid">f04e1870-e702-4d0c-a416-97dafe8ffe02</attr></set>
<set var="UserMapping[]"><attr name="name">Alison Wadle</attr><attr name="guid">1ef2ba32-735d-4019-a268-b3810832f991</attr></set>
<set var="UserMapping[]"><attr name="name">Andrew Auerbach</attr><attr name="guid">925566bd-90eb-46b9-ae4c-a8e6dfeccdb1</attr></set>
<set var="UserMapping[]"><attr name="name">Angie Filla-Kasim</attr><attr name="guid">c4e6996c-b53d-415b-bd76-1fbec0a2b08f</attr></set>
<set var="UserMapping[]"><attr name="name">Annie Presley</attr><attr name="guid">2b716550-f4a6-49b9-937f-f6e3c65e5de3</attr></set>
Then we repeat the same steps for all kinds of users’ spellings from the provided .xls file. All of these lines were generated by adding the cells in Excel.
We call the script Usermapping.xml at the beginning of the main script and load this bulk in memory. Here is the beginning of the listing for the main script:
<?xml version="1.0" encoding="UTF-8"?>
<operations>
<set var="crmServer">crmdev</set> <!-- Set variable with name of profile for connection to CRM -->
<set var="sqlServer">sql</set> <!-- Set variable with name of profile for connection to SQL -->
<set var="isLogMode">{true}</set> <!-- Set boolean variable to specify script execution mode, this is sometimes required for testing purposes. -->
<set var="ImportUserId">f04e1870-e702-4d0c-a416-97dafe8ffe02</set> <!-- Set variable with GUID of user under which all records should be created in CRM -->
<include name="UserMapping"/> <!-- Loads script with array that contain required User mappings -->
<!-- Fill array with mapping for standard fields that doesn't require special logic. E.g. string, date, two options -->
<set var="OppMapping[]"><attr name="crm">name</attr><attr name="sql">Target</attr></set>
<set var="OppMapping[]"><attr name="crm">ccrm_datereceivedbymarketing</attr><attr name="sql">DateRFP</attr><attr name="type">date</attr></set>
<set var="OppMapping[]"><attr name="crm">estimatedclosedate</attr><attr name="sql">DueDate</attr><attr name="type">date</attr></set>
<set var="OppMapping[]"><attr name="crm">ccrm_typeofsubmission</attr><attr name="sql">TypeOfSubmission</attr></set>
<set var="OppMapping[]"><attr name="crm">ccrm_nonpracticerelatedquestions</attr><attr name="sql">DescriptionNon</attr></set>
The bulk with a comparison of the field in SQL and CRM must be set for the fields, which can be simply filled in with the built-in method of CRM Migration Tool (powered by Wave.Integrate platform). Then we read the records from SQL and start processing all the special values. Let's look at the structure of the resulting script:
<read in="{sqlServer}" var="dbRecords"/> <!-- Read all records from SQL into array variable, for each record add all columns as attributes -->
<context for="crm" user="{ImportUserId}"> <!-- Set execution context for CRM -->
<for var="dbRecord" in="dbRecords"> <!-- Starts loop that does through each record in array -->
<!-- Set variables for lookups, these should be emptied for each record, so it placed in the beginning of cycle -->
<set var="crmAccountId">{null.guid}</set>
<set var="crmContactId">{null.guid}</set>
<set var="crmUserId">{null.guid}</set>
<if condition="{dbRecord['Target'].isSet and dbRecord['Target'].Length gt 0}">
<!-- Process account lookup -->
</if>
<if condition="{dbRecord['LeadAttorney'].isSet and dbRecord['LeadAttorney'].Length gt 0}">
<!-- Process user lookup -->
</if>
<log>Create opportunity</log>
<if condition="{!isLogMode}"/> <!-- Creation of opportunity record -->
<if condition="{dbRecord['AttorneyTeam'].isSet and dbRecord['AttorneyTeam'].Length gt 0}">
<!-- Process user Many-To-Many -->
</if>
<if condition="{dbRecord['CSGsIndustry'].isSet and dbRecord['CSGsIndustry'].Length gt 0}">
<!-- Process custom entity Many-To-Many -->
</if>
<if condition="{dbRecord['FollowupNotes'].isSet and dbRecord['FollowupNotes'].Length gt 0}">
<!-- Create Note for opportunity record -->
</if>
<if condition="{dbRecord['MarketingC'].isSet and dbRecord['MarketingC'].Length gt 0}">
<!-- Set owner of created opportunity record -->
</if>
</for>
</context>
</operations>
Plus sign next to the line means that this is the beginning of the operator. There are rows inside, but they were collapsed.
Data reading, lookup processing, record creating
Now let's see how to implement each individual part. Firstly we need to read all the records from SQL:
<read in="{sqlServer}" var="dbRecords"> <!-- Read all records from SQL into array variable, for each record add all columns as attributes -->
<query> <!-- Operator for SQL query, we just select all columns from table without any condition -->
SELECT * FROM [dbo].[opptable]
</query>
<for var="map" in="OppMapping"> <!-- Starts loop that goes through mapping in predefined array -->
<attr name="{map.sql}" /> <!-- Add attribute with name of SQL field for each column, that's important step that will be used later -->
</for>
</read>
An important point: we fill the pointers in the bulk with the fields’ names, using the prepared bulk. This is necessary in order to list all the fields with the help of the cycle when you create a record. But let's not get ahead of ourselves; let's move on to the next part of the code.
<if condition="{dbRecord['Target'].isSet and dbRecord['Target'].Length gt 0}"> <!-- Check that account column contain data only if so start process -->
<read in="{sqlServer}" entity="account" var="accounts"> <!-- First we are trying to find existing record in CRM -->
<criteria>
<attr name="name">{dbRecord['Target']}</attr> <!-- We use condition for account name, since we don't have any other information in source Excel file -->
</criteria>
<attr name="accountId" /> <!-- The only attribute we need is GUID of record -->
</read>
<if condition="{accounts.count gt 0}"> <!-- Check if account record was found -->
<log>Found Account by exact match</log>
<set var="crmAccountId">{accounts[0].accountId}</set> <!-- Fill the lookup variable with appropriate GUID -->
</if>
<if condition="{!crmAccountId.isSet}"> <!-- Check if lookup variable contains GUID, if no that means that we didn't found account in CRM and should create new record -->
<log>Creating new Account</log>
<if condition="{!isLogMode}"> <!-- If execution mode if Logging only, will not create account -->
<create in="crm" entity="account" var="crmAccountId"> <!-- Create new account and automatically populate lookup variable with GUID -->
<attr name="name">{dbRecord['Target']}</attr>
</create>
</if>
</if>
</if>
That's the first difference between the standard import and CRM Migration Tool (powered by Wave.Integrate platform): we are not only trying to find a record in CRM, but in case of its absence we create a new one. Go ahead.
<if condition="{dbRecord['LeadAttorney'].isSet and dbRecord['LeadAttorney'].Length gt 0}"> <!-- Check that user column contain data and only if so start the process -->
<for var="mapuser" in="UserMapping"> <!-- Starts loop that goes through each record in predefined array of Users -->
<if condition="{dbRecord['LeadAttorney'] eq mapuser.name}"> <!-- If user name in record match to user name in array -->
<log>User found in list</log>
<set var="crmUserId">{mapuser.guid}</set> <!-- Fill the lookup variable with appropriate GUID -->
</if>
</for>
</if>
Here we do not need to read records from the CRM, as we use the prepared bulk with the comparison of users. And now, most importantly, the creation of the record itself:
<if condition="{!isLogMode}"> <!-- Create opportunity record only if corresponding execution mode is set -->
<create in="{crmServer}" entity="opportunity" var="opportunityId"> <!-- Instruct engine to create record and populate variable with GUID of it -->
<for var="map" in="OppMapping"> <!-- Starts loop that goes through fields mapping in predefined array -->
<if condition="{map.sql ne 'FeeProposal'}"> <!-- Check that field is not Option set that requires special logic -->
<attr name="{map.crm}" type="{map.type.isSet ? map.type : 'String'}">{dbRecord[map.sql]}</attr> <!-- Add Attribute with name from mapping and type if specified or use String type by default -->
</if>
<if condition="{map.sql eq 'FeeProposal'}"> <!-- Check that field is Option set that requires special logic -->
<if condition="{(map.sql eq 'Alternative Fee') or (map.sql eq 'Hourly Rate')}"> <!-- If that field is have value that exists in option set -->
<attr name="{map.crm}" type="{map.type.isSet ? map.type : 'String'}">{dbRecord[map.sql]}</attr> <!-- populate option set field in CRM -->
</if>
<if condition="{(map.sql ne 'Alternative Fee') and (map.sql ne 'Hourly Rate')}"> <!-- If that field is have value that not exists in option set -->
<attr name="description" type="String">{dbRecord[map.sql]}</attr> <!-- populate value in description field instead of option set field in CRM -->
</if>
</if>
<if condition="{crmAccountId != null.guid}">
<attr name="customerId">account:{crmAccountId}</attr> <!-- populate lookup for account -->
</if>
<if condition="{crmUserId != null.guid}">
<attr name="ccrm_leadattorneyid">systemuser:{crmUserId}</attr> <!-- populate lookup for user -->
</if>
</for>
</create>
</if>
Association of the records with Many-To-Many and note creation
There is a special method in CRM Migration Tool (powered by Wave.Integrate platform) in order to create Many-To-Many relationship between two records. New methods could be easily added to a processor core if it is necessary.
<if condition="{dbRecord['AttorneyTeam'].isSet and dbRecord['AttorneyTeam'].Length gt 0}"> <!-- Check that Many-To-Many user column contain data and only if so start the process -->
<set var="Attorneys" type="String[]" split=",">{dbRecord['AttorneyTeam']}</set> <!-- Create array from field value, by splitting field value separated by commas -->
<for var="Attorney" in="Attorneys"> <!-- Starts loop that goes through each value in created array -->
<set var="crmAttorneyId">{null.guid}</set> <!-- Set variable for many-to-many record as empty in the beginning of cycle -->
<for var="mapuser" in="UserMapping"> <!-- Starts loop that goes through each record in predefined array of Users -->
<if condition="{Attorney.trim eq mapuser.name}"> <!-- If user name in record match to user name in array -->
<log>Found Attorney for Attorney Team</log>
<set var="crmAttorneyId">{mapuser.guid}</set> <!-- Fill many-to-many variable with appropriate GUID -->
</if>
</for>
<if condition="{crmAttorneyId != null.guid}"> <!-- If GUID for record is set -->>
<log>Associate Attorney with Opportunity</log>
<set var="Field1">opportunityId</set> <!-- Set name of the first entity in Many-To-Many -->
<set var="Field2">systemuserid</set> <!-- Set name of the second entity in Many-To-Many -->
<set var="RelName">cpdc_opportunity_systemuser</set> <!-- Set name of the Many-To-Many -->
<if condition="{!isLogMode}">
<set>{Crm5Utils.AssociateEntities('crm',opportunityId.string.Guid,Field1,crmAttorneyId.string.Guid,Field2,RelName)}</set> <!-- Use special method to create association -->
</if>
</if>
</for>
</if>
What can we see from this part of the script? First of all, we have a bulk of records. These records have to be associated by the split method. Then we are looking for a corresponding value of the obtained record in the same bulk, which was loaded from UserMapping script. After that we associate the Opportunity and the User, using the method of creation Many-To-Many relationship.
<if condition="{dbRecord['CSGsIndustry'].isSet and dbRecord['CSGsIndustry'].Length gt 0}"> <!-- Check that Many-To-Many custom entity column contain data and only if so start the process -->
<set var="Industries" type="String[]" split=",">{dbRecord['CSGsIndustry']}</set> <!-- Create array from field value, by splitting field value separated by commas -->
<for var="Industry" in="Industries"> <!-- Starts loop that goes through each value in created array -->
<set var="crmIndustryId">{null.guid}</set> <!-- Set variable for many-to-many record as empty in the beginning of cycle -->
<if condition="{Industry.Length gt 0 and Industry ne ''}"> <!-- Check that record name is not empty -->
<read in="{crmServer}" entity="ccrm_practicearea" var="practiceareas"> <!-- Trying to find record in CRM -->
<criteria>
<attr name="ccrm_name">{Industry.trim}</attr>
</criteria>
<attr name="ccrm_practiceareaid" />
</read>
<if condition="{practiceareas.count gt 0}"> <!-- If record found in CRM -->
<log>Found practice area by exact match</log>
<set var="crmIndustryId">{practiceareas[0].ccrm_practiceareaid}</set> <!-- Populate variable with GUID of record -->
<!-- Fill the lookup variable with appropriate GUID -->
</if>
<if condition="{!crmIndustryId.isSet}"> <!-- Check if lookup variable contains GUID, if no that means that we didn't found record in CRM and should create new record -->
<log>Creating new Practice Area</log>
<if condition="{!isLogMode}">
<create in="crm" entity="ccrm_practicearea" var="crmIndustryId"> <!-- Create new record and automatically populate lookup variable with GUID -->
<attr name="ccrm_name">{Industry.trim}</attr>
</create>
</if>
</if>
<if condition="{crmIndustryId.isSet}">
<log>Associate Practice Area with Opportunity</log>
<set var="Field1">opportunityId</set> <!-- Set name of the first entity in Many-To-Many -->
<set var="Field2">ccrm_practiceareaid</set> <!-- Set name of the second entity in Many-To-Many -->
<set var="RelName">cpdc_opportunity_practicearea</set> <!-- Set name of the Many-To-Many -->
<if condition="{!isLogMode}">
<set>{Crm5Utils.AssociateEntities('crm',opportunityId.string.Guid,Field1,crmIndustryId.string.Guid,Field2,RelName)}</set> <!-- Use special method to create association -->
</if>
</if>
</if>
</for>
</if>
This part of the script looks similar to the previous one, but in contrast to the users, we first try to find a record in CRM for this entity, and if we cannot find it, we create a new one. You may have noticed that the script often repeat specific structure and it is easy to use once written code. The only thing you have to do is just change the names of variables and fields’ values . Thus, the same script can be easily modified for importing of other entities.
<if condition="{dbRecord['FollowupNotes'].isSet and dbRecord['FollowupNotes'].Length gt 0}"> <!-- Create Note for opportunity record -->
<log>Create note for opportunity</log>
<if condition="{!isLogMode}">
<create in="crm" entity="annotation">
<attr name="notetext">{dbRecord['FollowupNotes']}</attr> <!-- Populate note text -->
<attr name="objectid">opportunity:{opportunityId}</attr> <!-- For successful creation we should specify name of entity and GUID with colon between -->
<attr name="objecttypecode">{3}</attr> <!-- Also we need to specify objecttypecode of entity for which note should be created -->
</create>
</if>
</if>
The last step: we create a note for the created record.
We have examined a script sample which was successfully used for the specific task of the customer. Wonderfulness of this solution is that it can be easily used for the import of new data, and in fact it happened so that a month later the customer came to us with a request to import new data in the same format, and we only had to spend time on preparation steps. We should admit that certain operations (such as the search for a record, or creation of a new entity, or cycle for processing records) can be easily separated from the script. It is very easy to modify existing scripts for other tasks. It is much easier than creating a new script from scratch.
Let us tell you more about our projects!
Сontact us:
hello@wave-access.com
