Your very own CSV file parser

So I recently found that apparently there's no support for the Microsoft Text Driver on 64 bit machines. In a nutshell, that meant all my applications which relied on it to parse CSV files and return them as a DataTable no longer worked. So up went the sleeves and out came this code snippet to manually do that parsing. This code makes the assumption that the CSV file is produced by Excel, the first row is a header row and there are no "s to encapsulate cells in the header row (all assumptions I could make in the process of writing this because I needed a quick fix). Hopefully this is easy enough to understand and modify for your own needs, but feel free to ask questions!



            
            string COMMA = "INCCOMMAREPLACEMENT57";
            string SEPA = "INCSEPARATOR57";
            string[] splitstring = new string[] { SEPA };

            DataTable t = new DataTable();
            string[] lines = File.ReadAllLines(csvDirectory + "\\" + csvFileName);//read in file
          
            //first line is header
            string[] headers = lines[0].Split(',');
          
           //for each subsequent row
            //for row 0: add them as column headers - we know line 0 has no ""            
            foreach (string s in headers)
            {
                t.Columns.Add(s, typeof(string));
            }

            //foreach line
            for (int i = 1; i < lines.Length; i++)
            {                
                int first = 9999;
                int second = 9999;
                string s = lines[i];

                //continue doing this until no more "" in the line
                while (first > -1)
                {
                    //search for "
                    first = s.IndexOf('"');

                    if (first > 0)
                    {
                        //search for closing "
                        second = s.IndexOf('"', first + 1);
                        string subS = s.Substring(first, second - first + 1);

                        //between the two "" find all , and replace with INCCOMMAREPLACEMENT57
                        string subS2 = subS.Replace(",", COMMA);

                        //remove the two ""
                        subS2 = subS2.Replace("\"", "");

                        s = s.Replace(subS, subS2);
                    }
                    else
                    {
                        break;
                    }
                }
                //replace all , with INCSEPARATOR57
                s = s.Replace(",", SEPA);
                
                //replace INCCOMMAREPLACEMENT57 with ,
                s = s.Replace(COMMA, ",");

                //split the line using INCSEPARATOR57
                string[] data = s.Split(splitstring, StringSplitOptions.None);
                
                DataRow r = t.NewRow();

                for (int j = 0; j < data.Length; j++)
                {
                    r[j] = data[j];                    
                }
                t.Rows.Add(r);
            }
return t;

Firing callouts on receipt of emails into a CRM system

Here's a cool little callout we wrote over here at Increase to create a new contact whenever we receive an email from an unknown person into our CRM system. It'll check to make sure it's not creating any duplicate contacts, and you can extend it to do all sorts of things with the contact that sent you that email. You could create a support case, fire off a thank you email, add them to a marketing list...completely up to you, really! I assume you know how to write a callout so here's the code we used. Register this callout on the post stage of the DeliverIncoming message to fire on receipt of an incoming email. You could also adapt this to fire on the DeliverPromote, which fires when you track an email from Outlook into CRM. Basically, if you use your imagination, emails in CRM will never be the same again! Code as below:


using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Query;
using System.Diagnostics;
using Microsoft.Crm.SdkTypeProxy;

public class CreateContact : IPlugin
{
public void Execute(IPluginExecutionContext context)
{
Guid emailId = new Guid();
if (context.MessageName == MessageName.DeliverIncoming)
{
emailId = (Guid)context.OutputParameters.Properties["EmailId"];
}
CrmService incService = context.CreateCrmService();
email thisEmail = (email)incService.Retrieve(EntityName.email.ToString(), emailId, new AllColumns());
Guid contactid = new Guid();
//Ensure this email is from someone
if (thisEmail.from != null && thisEmail.from.Length > 0)
{
activityparty from = thisEmail.from[0];
if (from.partyid == null)
{
//I've noticed that sometimes even though the partyid is null there's already a contact with the related email address, so do an additional check just in case
ColumnSet contactcol = new ColumnSet();
contactcol.AddColumn("emailaddress1");
contactcol.AddColumn("contactid");
QueryByAttribute q = new QueryByAttribute();
q.Attributes = new string[] { "emailaddress1", "statecode" };
q.ColumnSet = contactcol;
q.EntityName = EntityName.contact.ToString();
q.Values = new object[] { from.addressused, ContactState.Active.ToString() };
BusinessEntityCollection contacts = incService.RetrieveMultiple(q);
//That should be a list of all active contacts with the particular emailaddress1, extend this to as many emailaddress fields as you like
if (contacts.BusinessEntities.Count > 0)
{
if (contacts.BusinessEntities.Count == 1)
{
//So there actually is a contact with this email address
contactid = ((contact)contacts.BusinessEntities[0]).contactid.Value;
}
else
{
//You might want to flag up the fact that there's more than one contact with this email address
contactid = ((contact)contacts.BusinessEntities[0]).contactid.Value;
}
}
else
{
//We'll need to create a new contact with this email address
contact c = new contact();
c.emailaddress1 = from.addressused;
c.lastname = from.addressused.Remove(from.addressused.IndexOf('@'));
contactid = incService.Create(c);
}
//we will need to add the partyid no matter what since it is currently null and that shouldn't be the case
thisEmail.from[0].partyid = new Lookup();
thisEmail.from[0].partyid.Value = contactid;
thisEmail.from[0].partyid.type = EntityName.contact.ToString();
incService.Update(thisEmail);
}
else
{
contactid = from.partyid.Value;
}
//Do all sorts of cool stuff with the contact id you've just extracted/created.
}
}
}


Microsoft CRM on the Nintendo Wii?

So a completely random (and probably pointless) thought occurred to me as I was walking home from the Lab last night. Would we be able to get Microsoft CRM working on the Wii's Internet channel? I managed to check my work email on the Wii the other day, really loving the wireless keyboard support I recently discovered (it's great - I now keep the keyboard next to the sofa, and we never let a pointless argument last longer than 5 minutes before pulling it out and doing a quick Goog Live Search and proving the superiority of my opinion), and how cool would it be to be able to use CRM on the Wii? And then I realised - CRM does a browser compatibility check, and if it didn't work on Firefox, my guess is it won't work on Opera. But then homebrew applications allow for all sorts of unsupported custom apps running on the Wii, surely something similar would allow for MS CRM?

But why would people want to use a gaming machine for CRM? Check on campaign responses in between games of Mario Kart or Wii Sports tennis at home? Or would it be THE ultimate excuse to get a Wii for the office? Honestly speaking, it probably would be quite a pointless exercise, so why try it? My answer to that would have to be because I can. And that's when another thought struck me. I am an addict.

Hiding borders on IFrames with Related Regarding objects

I won't bore you with the details of how to add an iFrame that will display related regarding objects on the parent object's form, and why you might want to do that - there've already been plenty of blogs out there about that (Eg Huib Aarts' article on the subject). However, I've noticed that when I implemented my version of it, the pesky borders wouldn't go away, and the article linked to has some code in it that is meant to make the borders disappear - unfortunately, it didn't work for me, and I'm not sure why. So rather than sit through and debug it, I modified that code so that now, instead of modifying the style elements of those tables, it just rips up the child table with the important information and dumps it in the main body of the iFrame. Problem solved. There's still a slight border to the right that I can't get rid of, but if you turn the border displays off it's barely noticeable and I can live with that.

Here's the code if you're interested:

var frameName = 'IFRAME_Name';
var objFrame = document.getElementById(frameName);
var objWindow = document.frames[frameName];
objFrame.allowTransparency=true;
objFrame.onreadystatechange = function ()
{
if (objWindow.document.readyState=='complete')
{
objWindow.document.body.style.backgroundColor='transparent';
if (objWindow.document.getElementsByTagName('BODY')[0]!=null)
{
objWindow.document.getElementsByTagName('BODY')[0].style.padding='0px';
if (objWindow.document.getElementsByTagName('TABLE')[0] != null)
{
if (objWindow.document.getElementsByTagName('TABLE')[0].getElementsByTagName('TD')[0] != null)
{
objWindow.document.getElementsByTagName('BODY')[0].innerHTML = objWindow.document.getElementsByTagName('TABLE')[0].getElementsByTagName('TD')[0].innerHTML;
}
}
}
}
}

Calling CRM Webservices via Javascript

I used to be really intimidated by the idea of calling the CRM Webservice via JavaScript, but recently I have had the opportunity (read: didn't have a choice) to use it a lot, both in internal projects and for customer systems. And I realise now how stupid I was in keeping my head in the sand - the JS required to call the webservice does LOOK rather intimidating...but with some help from a fantastic tool on Stunnware (http://www.stunnware.com/crm2/topic.aspx?id=JSWebService), I soon found that, much like myself, it looks a lot worse than it actually is. I've now got a copy of that tool on my desktop set up so that I can quickly switch between the CRM3 and CRM4 versions, and am actually starting to learn to write the XML from scratch (or at least edit it to do different things without having to go through the tool).

There's just so many possibilities that this opens up. Some of the applications we've had for it include:

• Counters for related records (much like Outlook mail folders) - one caveat is that the more of these counters you want to have, the higher the overhead and therefore the longer the page takes to load
• Autopopulating fields - for example populating a Customer field with the parent customer of a responsible contact in the Incident entity upon population of the responsible contact
• Summarising details from other entities on the header of another entity - best explained with a screenshot:
  The circled data is data pulled from 2 different entities, linked by the contact - and it allows our support desk personnel to quickly find out the support level on a given customer's contract.

I'd love to hear of any other applications you might have come up with for this!