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