Welcome to KnowledgeLink - The AKG Blog

Powershell, csv files, and CRM REST with oData

Posted by Molly Sabol on Wed, Jul 30, 2014 @ 14:07 PM

Powershell, csv files, and CRM REST with oData

Alex, one of AKG's Software Engineers, wrote this blog post to share what he has been working on. Take a look!

I’ve been working on an interesting CRM project recently.  Basically, in our enterprise environment, our client wants every user to be a “Contact”.  The ideal solution would be to somehow point CRM’s Contact entity to our AD, but that’s not something you can currently do within CRM (at least with CRM 2011, which is what we’re using).  So our solution was to get a weekly .csv file of every user and push them into CRM’s Contacts entity with a Powershell script.  Although it would probably be easier and quicker to simply push the data into the SQL DB, I’m going to follow Microsoft’s recommended practice and use the web services, specifically the REST API.

 

First thing we need to do is to figure out which fields you want to use within the Contact entity and how they map to the fields within the csv file.  We’re going to keep this example simple, so let’s assume we want: first name, middle name, last name, office phone and email; all fields which already exist within the Contact entity.  Using the default Contact form, I’ve identified the fields we’ll be populating::

Powershell blog image 1

 

 

 

 

 

 

 

 

 

And that’s basically it for the work within CRM; the rest of the work is going to be within a Powershell script.  I love Powershell for things like this, a process that runs at a set schedule without any user interaction.

 

Let’s take a step back and understand what our process is going to be.  We’ll first get the csv file that contains our users.  For each user (each row in the csv), we’ll see if they already exist; if they don’t, we’ll insert the user as a new Contact.  If they do, we’ll update the Contact.  Not a ton to it, so let’s fire up Powershell ISE and get started.

 

Our first step is to access the csv file.  The path for the file used in this demo will be: C:\Demo\Users.csv

 

and contains the following::

 

FirstName,MiddleName,LastName,Email,OfficePhone

George,,Washington,gwashington@test.com,202-555-1111

John,,Adams,jadams@test.com,202-555-1212

Thomas,,Jefferson,tjefferson@test.com,202-555-9999

James,,Madison,jmadison@test.com,202-555-5555

James,,Monroe,jmonroe@test.com,202-555-8567

John,Quincy,Adams,jqadams@test.com,202-555-9898

Andrew,,Jackson,ajackson@test.com,202-555-0060

 

Powershell has this great cmdlet named Import-CSV which allows you to access a csv file and work with each row using dot notation (assuming your csv data has headers as the first row, which ours does).  So, running the following script:

 

clear-host

$rows = Import-Csv "C:\Demo\Users.csv"

foreach($r in $rows){

    write-host $r.FirstName $r.LastName $r.Email

}

 

Produces the following output:

Powershell blog image 2

 

 

 

 

 

 

As you can see, accessing the data is super easy.

 

But wait! There's more. To view the rest of this blog post, click on the link below!

Get the complete blog!

 

Topics: sharepoint blog, SharePoint, Applied Knowledge Group, best practices, Microsoft, CRM, implementation, PowerShell, oData, CRM REST, csv files