Opening CSV Files in .Net

One of the most common formats to get data exported from other systems in is the CSV (comma separated values) format. A good starting point for this pseudo standard is found here: Wikipedia on CSV. As this format is not published by any standards body, you may find some files are easier to open than others. However, most spreadsheets can open the format and massage the data as needed.

The simplest solution to opening these files using code in a Windows environment is to use the OleDb connection strings. However, I have seen quite a few different approaches, including some who are attempting to reference the file name in the Data Source string. That approach is limiting in that you have to create a new connection for each table and you can't perform joins.

Here is a simple C# (under 2.0, although none of the 2.0 features are used) snippet for opening a CSV. The NUnit test shows an example of a call to the function, although here we are only testing to ensure no exceptions were thrown.

using System;
using System.Data;
using System.Data.OleDb;
using NUnit.Framework;

namespace ConvertToCode
{
public class CsvLoader
{
private OleDbDataReader m_reader;

public OleDbDataReader Reader
{
get { return m_reader; }
}

private Exception m_exception;

public Exception Exception
{
get { return m_exception; }
}

public CsvLoader(string connString, string sql)
{
OleDbCommand command;
OleDbConnection conn;

conn = new OleDbConnection(connString);
command = new OleDbCommand();
try
{
conn.Open();
command.Connection = conn;
command.CommandText = sql;
command.CommandType = CommandType.Text;
m_reader = command.ExecuteReader();
}
catch ( Exception ex )
{
m_exception = ex;
}
}
}

[TestFixture]
public class CsvLoaderTest
{
[Test]
public void TestLoad()
{
string conn =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=d:\temp\;" +
@"Extended Properties=""Text;HDR=Yes;FMT=Delimited""";
string sql = "select * from DXREF2007.csv";
CsvLoader loader = new CsvLoader(conn, sql);
Assert.IsNull(loader.Exception);
}
}
}