CSV parsing – the easy way!

Every now and then we have a client that needs to, on a regular basis, import data from one system to another and a lot of the time we go for the good old csv file. In the past i have always just written a simple function that reads each line of the file, splits it into cells and import it into the database. But this simple code never fully handles csv’s correctly. I kept thinking “there has to be a simpler way“.

So i got thinking…

Csv files are just like data tables, data tables can be queried… surely there must be a way to just treat this file as a datasource. After a bit of playing around and reading i can up with the following solution.

It requires that the csv data be saved to a file and then we can just use an OleDataAdapter to perform almost any simple SQL statment against it!

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

public static DataTable ReadCSVFileIntoDataTable(string pFilePath)
{
    string fullPath = Path.GetFullPath(pFilePath);
    string file = Path.GetFileName(fullPath);
    string dir = Path.GetDirectoryName(fullPath);

    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
        + "Data Source=\"" + dir + "\\\";"
        + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

    string query = "SELECT * FROM " + file;

    DataTable dt = new System.Data.DataTable();
    OleDbDataAdapter da = new OleDbDataAdapter(query, connString);

    try
    {
        da.Fill(dt );
    }
    catch (InvalidOperationException /*e*/)
    { }

    dAdapter.Dispose();

    return dt;
    }

Related posts:

1. Parsing CSV files in .NET
2. Creating a generic settings repository in C#
3. Kentico, Smart Search and filtering attachments
4. Asp.Net MVC convert View to Word Document
5. Limiting LINQ String Field Lengths

Tags: parse, split