Import-ant Tool

Motivation

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.

In this post, we implement a small F# script to import data into Microsoft Dynamics CRM. However, if the data is already in the CRM system the script corrects the existing record with the new data, thereby preserving relations already in place. This means, we can start using our data immediately, without fear of loss.

Remember: this is a technical post; a usage/tutorial post will follow next week.

Prerequicits

For once we are not working with XML, thus we only need:

• F#, TypeProviders (in particular the XrmDataProvider)
• Microsoft Dynamics CRM
• And our own FetchXML library.

Utilities

Apart from the FetchXml library we made last week, we only need this simple function:

let rec assoc key map = 
  match map with
    | [] -> None
    | (k, v) :: map' ->
      if k = key
      then Some v
      else assoc key map'

The Library

Our goal is to load data from a CSV-file (comma-separated values) into CRM.

It is straightforward to load in strings. However, it is a different story for option sets or lookups. Option sets are easier if we have them as the values, as opposed to the labels. Although, this makes it much less readable, and harder to work with.

Another goal is to keep the data as human readable as possible. Readable data is easier to work with, and less error prone. It also allows less technical users to work with the data, e.g. in Excel.

Because we also want the library to be able to update records, which are already in CRM, therefore we need some way to specify which records to update.

My favorite solution to these problems is to use sophisticated headers. Our headers should specify both what type of data it is, and supply additional information necessary to load the data. The headers should also specify which attributes uniquely define a record; this could be an account number, or an email address. We call these fields primary.

Initialization

There are many types of data in CRM. In this prototype, we only address a few very common types; strings, booleans, local option sets, global option sets, and lookups.

For simplicity, I have decided that you can only set lookups based on one attribute of the target entity. We also represent both local and global option sets simply as a mapping from labels to values. We also allow columns to be ignored.

type mapping =
  | Simple
  | Ignore
  | OptionSet of (string * int) list
  | Lookup of string * string
type field =
  { name : string;
    typ : mapping;
    primary : bool; }

In order to initialize the mapping we need to define some syntax:

• If a field starts with # the column will be ignored
• Otherwise the header should be the logical name of the attribute in CRM
• If the name is followed by a * this column is primary
• If the name (and possibly *) is followed by only a :, then the field is the label of a local option set. (Example (Account): paymenttermscode:)
• If the name (and possibly *) is followed by only a : and the logical name of a global option set, then the field is the label of that global option set. (Example (Opportunity): budgetstatus:budgetstatus)
• If the name (and possibly *) is followed by only a : and the logical name of an entity, and a . and the logical name of an attribute of the entity, then the field should look up a record of that entity based that attribute. (Example (Account): primarycontactid:contact.emailaddress1)

let init_mapping ent (s : string) =
  s.Split [|';'|]
  |> Array.map (fun f ->
    let f = f.Trim () in
    let f = f.Split(':') in
    let primary = f.[0].EndsWith("*") in
    let name = 
      if primary
      then f.[0].Substring(0, f.[0].Length - 1)
      else f.[0] in
    if name.StartsWith("#")
    then { name = name; typ = Ignore; primary = primary }
    else if f.Length = 1
    then { name = name; typ = Simple; primary = primary }
    else 
      let f = f.[1].Split('.') in
      if f.Length = 1
      then 
        let options =
          if f.[0].Length = 0
          then 
            let req = RetrieveAttributeRequest () in
            req.EntityLogicalName <- ent;
            req.LogicalName <- name;
            let resp = xrm.OrganizationService.Execute req :?> RetrieveAttributeResponse in
            let pList = resp.AttributeMetadata :?> PicklistAttributeMetadata in
            pList.OptionSet.Options
            |> Seq.map (fun o -> (o.Label.UserLocalizedLabel.Label, o.Value.Value))
            |> Seq.toList 
          else
            let req = RetrieveOptionSetRequest () in
            req.Name <- f.[0];
            let resp = xrm.OrganizationService.Execute req :?> RetrieveOptionSetResponse in
            let oSetMeta = resp.OptionSetMetadata :?> OptionSetMetadata in
            oSetMeta.Options
            |> Seq.map (fun o -> (o.Label.UserLocalizedLabel.Label, o.Value.Value))
            |> Seq.toList in
        { name = name; typ = OptionSet options; primary = primary }
      else
        let entity = f.[0] in
        let field = f.[1] in
        { name = name; typ = Lookup (entity, field); primary = primary }
    )

Setting Data

Now setting a value based on the mapping and a string is straightforward.

Notice: lookup is from last week’s post.

let set_value (e : Entity) m (v : string) =
  if v.Length = 0
  then ()
  else
    match m.typ with
      | Ignore -> ()
      | Simple -> 
        if v = "true" then
          e.Attributes.Add(m.name, true)
        else if v = "false" then
          e.Attributes.Add(m.name, false)
        else
          e.Attributes.Add(m.name, v)
      | OptionSet map -> 
        match assoc v map with
          | None -> printfn "Warning: %s not found in optionSet" v; ()
          | Some i -> e.Attributes.Add(m.name, OptionSetValue i)
      | Lookup (ent, f) ->
        e.Attributes.Add(m.name, EntityReference (ent, lookup ent f v))
  

Getting Data

As mentioned above we sometimes want to update existing records instead of creating new ones. The way we determine this 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. To test this we again use our FetchXml library.

Notice: we require that the record be unique. If we did not do this, we could not guarantee that we consistently update the same record.

let get_entity ent ms vals =
  let fxml = 
    (ms, vals)
    ||> Array.fold2 (fun st m v -> 
      if not m.primary
      then st
      else 
        match m.typ with
          | Ignore -> st
          | Simple -> FetchXml.add_condition m.name (FetchXml.Equals v) st
          | OptionSet os -> FetchXml.add_condition m.name (FetchXml.Equals (string (assoc v os).Value)) st
          | Lookup (ent, f) -> FetchXml.add_condition m.name (FetchXml.Equals ((lookup ent f v).ToString("B"))) st
      ) (FetchXml.init (Crm.Entities.unsafe ent) |> FetchXml.set_count 2)
    |> FetchXml.generate in
  let ec = xrm.OrganizationService.RetrieveMultiple (FetchExpression fxml) in
  let e = Entity () in
  e.LogicalName <- ent;
  if ec.Entities.Count = 0 then
    e
  else if ec.Entities.Count = 1 then
    e.Id <- (ec.Item 0).Id;
    e
  else
    printfn "Fxml: %s" fxml;
    failwithf "Lookup failed: multiple %s records found." ent

Importing/Repairing Data

With these helpers, the main function simply loads the file, line-by-line, and calls the helpers. Currently the library assumes the default export format for CSV files from Excel, which means un-quoted strings separated by semicolons.

let action act (e : Entity) =
  if e.Attributes.Contains("name")
  then printfn "%s: %s" act (string (e.Attributes.["name"]))
  else printfn "%s entity" act

let import_data entity =
  let lines = File.ReadAllLines (cfg.rootFolder + "/" + entity + ".csv") in
  let mapping = init_mapping entity lines.[0] in
  lines
  |> Array.skip 1
  |> Array.iter (fun valS ->
    try
      let vals = valS.Split [|';'|] |> Array.map (fun v -> v.Trim ()) in
      if vals.Length <> mapping.Length
      then failwithf "Not enough values"
      else
        let e = get_entity entity mapping vals in
        (mapping, vals)
        ||> Array.iter2 (set_value e);
        if e.Id = System.Guid.Empty
        then action "Created" e; xrm.OrganizationService.Create e |> ignore
        else action "Updated" e; xrm.OrganizationService.Update e
    with
      e -> printfn "Error: %s; %s" (e.Message) valS
    )

Quality Control

As I am devoted to high quality software, we should take a step back and examine how solid this library is.

First, this library is only as good as the data it gets. We can feed it destructive data, either because it overwrites good data, or contains invalid references, or something else. In practice though, bad data is most commonly rejected by CRM, in which case the tool skips the line, prints the error, and the faulty data. Similar to the built in import tool in CRM.

Another potential problem is if the data contains semicolons, in which case the input line is split incorrectly. However, there is no way to protect against this with the format we chose. If this happens, the line will be skipped, and printed.

Because of the primary fields, the tool never creates duplicates. This also means that we can run it multiple times without any additional risks. Further, because any error is printed, along with the triggering data, we get an easy overview over what has been skipped, and usually why. This means that we can easily find the flaw, correct it, and run the import tool again.