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