Import-ant Data

Previously on Dr. Lambda's blog:

Importing data into Microsoft Dynamics CRM is convenient; however, the data we have to import is usually not perfect, therefore, we often end up importing and deleting the same data many times before we are satisfied. Both of these processes are quite slow, and does require a bit of manual work. Another caveat of this method is that we cannot start using the data until it is imported correctly, because relations to it will be broken.

import : string -> unit

Now, the continuation...

Off the Shelf Usage

Our tool can import CSV-files into CRM. In order to do this however we need to follow strict rules.

The first rule is the name our CSV-file should be the logical name of the entity we want the records to have. Thus if we want to import contacts the file should be called contact.csv.

Further, the first line in the file should be headers, also following strict rules.

Field types

Normal Values

For string (or two option) values the header should be the logical name of the attribute.

Examples:

name
donotemail
drl_custom_attribute

Local Option Sets

For values which are labels of a local option set we add a : after the logical name of the attribute.

Examples:

paymenttermscode:
drl_customerpricegroup:

Global Option Sets

For values which are labels of a global option set we further add the logical name of the option set after the :.

Examples:

budgetstatus:budgetstatus
drl_attribute:drl_option_set

Lookup Fields

For values which refer to other entities we add a :, then the logical name of the target entity, then a ., then the logical name of the field to lookup based on.

Examples:

transactioncurrencyid:transactioncurrency.isocurrencycode
primarycontactid:contact.emailaddress1

Additional

Primary Keys

We can define a number of fields as primary. By doing this the tool will instead try to update records before it creates new ones. The way the tool determines what to do is if a record exist which is equal on all the primary fields to the one we would create, then we update this one instead.

We annotate a field as primary by adding a * immediately after its logical name.

Examples:

name*
primarycontactid*:contact.emailaddress1

Ignore

We can also have fields in our CSV files which contain data we don’t want in CRM. If we prefix a header with # the column will be ignored.

Examples:

# secret data
# password

Putting it together

Here is a toy example of a complete CSV file, ready for import.

contact.csv:

firstname ; lastname ; emailaddress1* ; parentcustomerid:account.name ; preferredcontactmethodcode: ; # age
Jacob ; Goldberg ; first@bing.com ; Microsoft ; Phone ; 43
John ; Doe ; untitled@gmail.com ; Google ; Email ; 28
Michael ; Lincoln ; michael82@hotmail.com ; Starbucks ; Mail ; 23
Christian ; Gosling ; real_gosling@live.com ; Blizzard ; Any ; 35