Poor Man's Method of Reading CSV File into DataTable

Introduction

Sometimes, you just need a quick way to read a CSV file and process the contents. There are many ways to do it, some of them using third-party tools, opensource libraries, regular expressions and the like. This tip provides a simple, native .NET method to read a CSV file into a System.Data.DataTable object. We won't focus on validation, checking if the file exists, parsing the content or anything like that and will assume that we're working with a well-formed .CSV file. As always, good error handling is king, but it isn't the objective with this tip.

Using the Code

The OpenCsvFileAsDataTable() method is listed below:

public DataTable OpenCsvFileAsDataTable(string fileName, bool firstLineIsHeader)
{
    DataTable result = new DataTable();
    System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
    
    // The table name is the actual name of the file.
    string tableName = fileInfo.Name;
    
    // Get the folder name in which the file is. This will be part of the 
    // connection string.
    string folderName = fileInfo.DirectoryName;
    string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;" +
                              "Data Source=" + folderName + ";" +
                              "Extended Properties=\"Text;" + 
                              "HDR=" + (firstLineIsHeader ? "Yes" : "No") + ";" + 
                              "FMT=Delimited\"";
    
    using (System.Data.OleDb.OleDbConnection connection =
        new System.Data.OleDb.OleDbConnection(connectionString))
    {
        // Open the connection 
        connection.Open();
            
        // Set up the adapter and query the table.
        string sqlStatement = "SELECT * FROM " + tableName;
        using (System.Data.OleDb.OleDbDataAdapter adapter =
            new System.Data.OleDb.OleDbDataAdapter(sqlStatement, connection))
        {
            result = new DataTable(tableName);
            adapter.Fill(result);
        }
    }

    return result;
}

Points of Interest

The directory in which the file is will be opened as a "database". Each of the CSV files in the directory can be queried as if they're a table.

It might not be the absolute best way of doing this, but it is a reasonably good, functional, poor-man's way to quickly read and consume the content of a CSV file. What is great about it is that you don't need much to get it working.

History

• 10^th July, 2015 - Initial tip