Creating and Parsing Excel Compatible CSV Files

True Comma Delimited CSV Files

by Les Smith

How do I create and parse a true Excel Compatible CSV file?  Excel is very particular about how it creates and parses CSV files.  Some folks have the idea that Excel encloses all fields in double quotes when it creates a comma delimited file.  At least with Excell 2003, that's not exactly true; in fact, not even close.

In a ASP.NET Web Application that I am working on, I had to export and import CSV files from/to database.  I found that my own concept of what Excel does in certain situations varies based on the presence of commas and double quotes, on both the Import and Export in Excel.  After testing numerous configurations of data in spreadsheet fields I found that the rule of thumb for creating a spreadsheet that Excel will load properly, is this.  If a field has one or more quotes (") in it, the number of quotes should be doubled.  Secondly, having taken care of that for each field, if the resultant field contains a quote or a comma, the field should be enclosed in double quotes.  Figure 1 will show the code for creating the proper quoting of fields for a CSV.

Figure 1 - Quoting Fields For Writing CSV.

foreach (DataRow dr in dt.Rows)     {        string line =          QuoteFieldAsRequiredByExcel(dr["FName"].ToString().Trim()) + comma +          QuoteFieldAsRequiredByExcel(dr["LName"].ToString().Trim()) + comma +          QuoteFieldAsRequiredByExcel(dr["PhoneNumber"].ToString().Trim()) +          Environment.NewLine;         Response.Write(line);    } // foreach

The method shown below will prepare a field for writing to a CSV file so that Excel will treat it properly upon loading the file into Excel.

Figure 2 - Quoting Method.

/// if a field contains a " or , the rule is that     /// all quotes are doubled and the field is inclosed in quotes     private string QuoteFieldAsRequiredByExcel(string field)     {         string retValue = field.Replace("\"", "\"\"");         if (field.IndexOf(",") > -1 || field.IndexOf('"') > -1)         {             return "\"" + retValue + "\"";         } // if         else         {             return field;         } // else     } // method: QuoteFieldWithComma

Now, what about parsing a true comma delimited file.  The obvious best choice for parsing a CSV file is the use of a Regular Expression.  Although I have tried and used several Regex Patterns, I have found the one used in the code shown below in Figure 3 to be the most consistently correct.  I found the Regular Expression Pattern in numerous places on the web, so it has been obviously copied numerous times, leaving me without knowing the original author.  Just know that I do not claim to be the author of this particular Regular Express Pattern for parsing a Comma Delimited CSV file.  In this code, the parsed fields will be used to populate a DropDownList of data from several lines of a CSV file.  The Regex Pattern is designed to find the commas that are outside any configuration of quotes.  Since, as seen above, Excel can create a variety of quoted scenarios, with interspersed commas, the Regex Pattern finds the commas that truly separate the various fields.

Figure 3 - Parsing Comma Delimited File Using Regex.Split.

string[] lineArray = null;    Regex re = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");    using (StreamReader sr = new StreamReader(VSFileName))    {        string line = sr.ReadLine();        sr.Close();        lineArray = re.Split(line);        foreach (string field in firstFields)        {            ddlFirstNames.Items.Add(field.Replace("\"", "").Trim());        } // foreach         }

If you only test the creation of a CSV file in one or two ways, you can easily be fooled into thinking that the handling of commas and quotes is fairly simple.  Actually, the rule is simple, as quoted in the comment lines in Figure 2 above, but the resultant field contents can be quite complex.

Need to automatically organize your code windows?  You'll be amazed how easy it is to keep the code in your code windows organized.  TRY IT FREE FOR 30 DAYS BY CLICKING HERE.

Automatically generate braces in C#! Try CSharpCompleter and stop wasting valuable time needlessly typing hundreds of braces {} daily.  Try CSharpCompleter for 30 DAYS FREE.

Ask a Question, or give your feedback on my articles or products by going to the KnowDotNet Forum or by clicking on My Blog.

You can also email me directly at Les@KnowDotNet.com.