CSV parsing using OLEDB

For a few days I did run into the problem of importing CSV data with OLEDB. The first thing I tried was creating my own parser, but later I found an easy built in way. I show both methods here.

My own parser is simple based on the split string functionality.

//create the table
DataTable dt = new DataTable("Import");
dt.Columns.Add("col1", typeof(string));
dt.Columns.Add("col2", typeof(string));
dt.Columns.Add("col3", typeof(string));
 
//read the data
using (StreamReader sr = new StreamReader(filepath))
{
    int i = 0;
    while (!sr.EndOfStream)
    {
        //skip the header
        while (i < 1)
        {
            sr.ReadLine();
            i++;
        }
 
        string[] text = sr.ReadLine().Split(',');
        DataRow dr = dt.NewRow();
        dr["col1"] = text[0];
        dr["col2"] = text[1];
        dr["col3"] = text[2];
 
        dt.Rows.Add(dr);
    }
}

The build in CVS parser uses a connection string and a SQL query to handle the CVS data. The connection string is configured according to:
• Provider: Jet OLEDB
• Data source: The directory that contains the CSV file
• Extended properties: ‘text’ means that we are parsing a text, the HDR property can be set to ‘Yes’ (the first row in the CSV files is header information) or ‘No’ (there is no header row), and setting the FMT property set to ‘Delimited’ essentially says that we will be working with a comma separated value file.

public static DataTable ParseCSV(string filePath)
{
    if (!File.Exists(filePath))
    {
        return null;
    }
 
    string fileName = Path.GetFileName(filePath);
    string dirName = Path.GetDirectoryName(filePath);
 
    //connection string 
    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="" + dirName + "\";" + "Extended Properties="text;HDR=No;FMT=Delimited"";
    string query = "SELECT * FROM " + fileName;
 
    DataTable dt = new DataTable();
    OleDbDataAdapter adapter = new OleDbDataAdapter(query, connString);
 
    try
    { 
        adapter.Fill(dt);
    }
    catch (InvalidOperationException)
    { }
 
    adapter.Dispose();
 
    return dt;
}
0.00 avg. rating (0% score) - 0 votes

About Peter Wibeck

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

*