Let’s say you exported a group of Contacts from CRM 2011 using the “Export to Excel” button and you selected the “Make this data available for re-importing by including required column headings” button. The important thing that this does is it adds the GUID for the contact to the first column.
Now, let’s say you have people edit this spreadsheet and make updates to contact data. Our workflow was that if you edited a contact, you highlighted the cell you modified, and marked the contact as modified (in a new column). All I would need to find all the columns/fields that have been modified (they are highlighted), and I should be able to loop through them and update the record via the GUID. I wrote the following VB function to generate the update string that will be exported with the GUID of the contact. This function looks for a cell that has a background color, then grabs the column heading for that field, and generates an update string. An example of an update string will look like this:
“{`”Telephone1`”:`”555-555-5555`”,`”Address1_Telephone1`”:`”555-555-5555`”}”
Function GenerateUpdateString(rRange As Range)
Dim rCell As Range
Dim vResult As String
vResult = "{"
For Each rCell In rRange
If rCell.Interior.ColorIndex > 0 Then
ColumnHead = Cells(1, rCell.Column).Value
vResult = vResult & "`""" & ColumnHead & "`"":`""" & Trim(rCell.Value) & "`"","
End If
Next rCell
If Right(vResult, 1) = "," Then
vResult = Mid(vResult, 1, Len(vResult) - 1)
End If
vResult = vResult & "}"
GenerateUpdateString = vResult
End Function
Now I just use the following powershell code to loop through the columns in the exported csv and update each record.
$CSVFile=import-csv -path c:\Contacts.csv
foreach ($line in $CSVFile){
$url="http://crm.server.com/Instance/xrmservices/2011/OrganizationData.svc/ContactSet(guid'$($line.GUID)')"
$webclient = new-object System.Net.WebClient
$webclient.UseDefaultCredentials = $true
$webclient.Headers.Add("Accept", "application/json")
$webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
$webclient.Headers.Add("X-HTTP-Method", "MERGE")
# EXample $line.UpdateString="{`"Telephone1`":`"555-555-5555`",`"Address1_Telephone1`":`"555-555-5555`"}"
$stringToUpload=$line.UpdateString
$resultString=$webclient.UploadString($url,$stringToUpload)
Waiting for some real data to try with, but so far, my small test data works. Will let you know how it works. What do you think?