Блог

Полезные статьи и новости о жизни WaveAccess

Импорт сложных EXCEL файлов в MS CRM

Часто возникает необходимость импортировать данные в CRM из другой системы, при этом формат экспорта из этой системы весьма ограничен. В этой статье мы рассмотрим, как с помощью SyncIT (решение, которое разрабатывает и поддерживает WaveAccess) мы осуществляли импорт для одного из наших клиентов именно в таких условиях. Весь листинг будет снабжен подробными комментариями.

Клиент обратился к нам за помощью с импортом Opportunities в CRM 2011 из другой системы. Формат, в котором были предоставлены данные — обычный excel файл с колонками. При этом CRM у клиента сильно кастомизирована, и сущность Opportunity имеет специальные связи Many-to-Many с кастом сущностью и Users, а также обязательные lookup и наборы полей. В предоставленном excel файле все данные были отображены на одном листе, связанные записи шли в одной колонке перечислением через запятую. К сожалению, стандартный импорт CRM довольно сильно ограничен в возможностях, в частности, он не может создать новую запись для лукапа, если она не найдена в CRM, а также не способен создавать Many-To-Many связи, поэтому было решено использовать наш специальный продукт SyncIT.

Подготовка данных

В каком виде были данные: одна строка из исходного excel файла.

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

Пояснение по колонкам: 1 – lookup на Account и название opportunity;  2 - владелец записи; 6 - lookup на Users; 7- Many-To-Many связь с Users; 8 - Many-To-Many связь с custom entity; 11 – Notes; 3,4,5,9,10,12 – обычные поля

Уже на основании этих данных можно заметить первую сложность: в колонках 2,6,7, где должны быть пользователи, данные не стандартизированы, имена не совпадают с пользователями в CRM. Поэтому предварительно была выполнена следующая операция: с помощью Excel  из этих трех колонок данные были скопированы в одну колонку (предварительно обработав колонку 7 функцией Text to Columns, использую разделитель запятую). Таким образом, в одной колонке у нас получились всевозможные варианты написания пользователей. Следующем действием были экспортированы GUID пользователей из CRM  и сопоставлены с получившимися данными. Каким образом это будет использовано в скрипте, мы рассмотрим далее. Вот пример строки из получившихся данных:

From List

Currently in CRM

SystemUserId Production system

SystemUserId Development

Conley

Steve Conley

D99435EC-7E92-E311-AC0A-005056922C86

E8508251-0F93-E311-82ED-0050569270F6

Как и у большинства компаний, у клиента есть две среды и, соответственно, две организации CRM, и импорт должен был быть произведен в обе организации. К счастью, единожды написав скрипт в SyncIT, можно использовать его для импорта бесчисленное множество раз и в разные организации, и в данном случае необходимо лишь поменять сопоставление записей пользователей на GUID’ы из организации для конкретной среды.

После такой предварительной подготовки данные без изменений были импортированы в SQL с помощью стандартной возможности SQL Management Studio.

Import Data,SQL Management Studio, импортировать данные, данные, SyncIT

Начало скрипта, маппинг полей, структура

Перейдем непосредственно к разбору скрипта, который мы использовали для импорта. Первым делом был создан файл Usermapping.xml, в котором заполняется массив сопоставления пользователей, используемый в дальнейшем основным скриптом. Нет смысла приводить весь листинг, но чтобы было понятно, вот начало скрипта с комментария:

<?xml version="1.0" encoding="UTF-8"?>
<operations> <!-- The main operator, instructs SyncIT 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>

И так далее, для каждого варианта написания пользователя из заранее приготовленного файла excel. Естественно, все эти строки не писались вручную, а были сгенерированы с помощью сложения ячеек в Excel.

В главном скрипте в самом начале мы вызываем скрипт Usermapping.xml и таким образом загружаем этот массив в память. Приведем начало листинга для основного скрипта:

<?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>

Для полей, которые можно просто заполнить с помощью встроенного метода SyncIT, задается массив с сопоставлением поля в SQL и CRM. После этого можно считать записи из SQL и приступать к обработке всех специальных значений. Давайте посмотрим на получившуюся структуру скрипта:

    <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>

Знак плюса напротив строки означает, что это начало оператора, внутри которого есть еще строки, просто они свернуты. Мы разберем отдельно каждую свернутую часть скрипта, но для удобства восприятия именно структуры, сначала приводим их в таком виде.

Считывание данных, обработка лукапов, создание записи

Давайте теперь посмотрим, как реализована каждая отдельная часть. Первым делом нам нужно прочитать все записи из 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>

Важный момент: мы заполняем указатели в массиве именем полей, используя подготовленный заранее массив. Это необходимо для того, чтобы в дальнейшем при создании записи можно было с помощью цикла перечислить все поля. Но не будем забегать вперед, перейдем к следующей части кода.

<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>

Вот первое отличие SyncIT от стандартного импорта: мы не только пытаемся найти запись в CRM, но в случае ее отсутствия создаем новую запись. Идем дальше.

<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>

Здесь нам уже не нужно читать записи из CRM, так как мы используем заранее подготовленный массив с сопоставлением пользователей. А теперь главное, создание самой записи:

<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>

Примечательно: в этой части обработка поля с типом набор полей. Дело в том, что исходные данные содержали значения для этого поля, которых нет в CRM. Заказчик попросил не создавать для таких значений новые поля, а вместо этого записать их значение в поле description. Такая задача тоже с легкостью решается с помощью SyncIT.

Ассоциируем записи с Many-To-Many и создаем заметку

Для того, чтобы создать Many-To-Many связь между двумя записями, в SyncIT существует специальный метод. Новые методы легко добавляются в ядро процессора при необходимости.

<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>

Что видно из этой части скрипта? Первым делом, мы получаем массив с записями, которые нужно проассоциировать, используя метод split. Далее для каждой полученной записи мы ищем соответствующее значение в том же массиве пользователей, который был загружен из скрипта UserMapping. И последним действием, используя метод для создания Many-To-Many, мы ассоциируем Opportunity и User.

<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>

Эта часть скрипта выглядит похожей на предыдущую, но, в отличие от пользователей, для этой сущности мы сначала пробуем найти запись в CRM, и если не находим, создаем новую запись. Вы уже могли заметить, что в скрипте часто повторяются определенные конструкции, и можно легко использовать написанный единожды код, изменив названия переменных и значения полей. Таким образом, этот же скрипт можно будет легко модифицировать для импорта других сущностей, но об этом в другой статье. 

<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>

Последним шагом мы создаем заметку для созданной записи, полагаем, комментарии тут излишни.

Мы разобрали пример скрипта, который был успешно использован для выполнения конкретной задачи заказчика. Замечательность такого решения в том, что он может быть без труда быть использован для новых данных, что по факту уже произошло (спустя месяц заказчик обратился к нам с просьбой проимпортировать новые данные в таком же формате,и в этот раз нам пришлось потратить время лишь на подготовительные шаги). Стоит также обратить внимание,  что так как определенные операции (такие, как поиск записи, или создание новой записи, или цикл для обработки записей) можно легко выделить из скрипта, очень просто модифицировать уже существующие скрипты для выполнения других задач. Зачастую это гораздо проще, чем создавать новый скрипт с нуля.

Заказать звонок

Удобное время:

Отменить

Пишите!

Присоединить
Файл не больше 30 Мб.
Отменить