Jul 232009
 

While building a Excel CSV to Claim application for a client I needed to take a comma delimited record and place it into a string array.

The challenge is that there is a wrap character only for the fields that have the delimiter in the data.

Example:

012346,012346001,SMITH              ,SCOTT,J
012345,012345001,”WILLIAMS, III                 ”,HENRY                 ,R
012344,012344001,DAHL                ,MICHELLE                      ,T

I could not find an easy way to parse this into an array, this is what I came up with:

public string[] Split(string expression, string delimiter, string qualifier, bool ignoreCase)
{
    string _Statement = String.Format("{0}(?=(?:[^{1}]*{1}[^{1}]*{1})*(?![^{1}]*{1}))",Regex.Escape(delimiter), Regex.Escape(qualifier));

    RegexOptions _Options = RegexOptions.Compiled | RegexOptions.Multiline;
    if (ignoreCase) _Options = _Options | RegexOptions.IgnoreCase;

    Regex _Expression = new Regex(_Statement, _Options);
    ArrayList finalresult=new ArrayList();
    foreach (string result in _Expression.Split(expression))
    {
        string tempData=result.Replace(qualifier, "");
        finalresult.Add(tempData.Trim());
    }
    string[] strArray = finalresult.ToArray(Type.GetType("System.String")) as string[];
    return strArray;
}

I am sure it can be done better, yes, I know, two passes through isn’t quite the best to remove the “ from the quoted field, and I could have done it a little better to remove the spaces, but it does what I needed, and the client was not paying for how fast it runs, he was paying me for how fast I could code it!