CSV import utility class.

CsvImportUtiltiy.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

/// <summary>
/// Improts csv data from a file.
/// </summary>
/// <remarks>
/// https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx
/// </remarks>
public class CsvImportUtility
{
    /// <summary>
    /// Initializes a new instance of a CsvImportUtility class.
    /// </summary>
    public CsvImportUtility()
    {
        Delimiter = ',';
    }

    /// <summary>
    /// Builds the connection string according to settings applied on the utility.
    /// </summary>
    /// <param name="filename"></param>
    /// <returns>
    /// IMEX=1; forces the driver to interpret all columns as text
    /// </returns>
    string BuildConnectionString()
    {
        string hdr = (this.HasHeaders) ? "Yes" : "No";
        string datasource = this.TempPath;
        string constr = "Provider=Microsoft.Jet.OLEDB.4.0;"
                      + string.Format("Data Source={0};", datasource)
                      + string.Format("Extended Properties=\"text;IMEX=1;\";", hdr, this.Delimiter);
        return constr;
    }

    /// <summary>
    /// Gets or sets the value indicating if the file contains headers.
    /// </summary>
    public bool HasHeaders { get; set; }

    /// <summary>
    /// Gets or sets the value that will be used as a column delimiter.
    /// </summary>
    public char Delimiter { get; set; }

    /// <summary>
    /// Gets or sets the name of the file that will be imported.
    /// </summary>
    public string Filename { get; set; }

    /// <summary>
    /// Gets or sets the number of columns that are expected to occur in the file. Null to auto discover columns.
    /// </summary>
    public int? SchemaColumnCount { get; set; }

    /// <summary>
    /// Gets or sets the temporary filepath used by the import utility.
    /// </summary>
    string TempPath { get; set; }

    /// <summary>
    /// Creates and writes the schema to the temp directory.
    /// </summary>
    void WriteSchema()
    {
        using (StreamWriter schema = new StreamWriter(Path.Combine(this.TempPath, "schema.ini")))
        {
            schema.WriteLine("[{0}]", Path.GetFileName(this.Filename));
            if (Delimiter == '\t')
            {
                schema.WriteLine("Format=TabDelimited", Delimiter);
            }
            else if (Delimiter == ',')
            {
                schema.WriteLine("Format=CSVDelimited", Delimiter);
            }
            else
            {
                schema.WriteLine("Format=Delimited({0})", Delimiter);
            }

            schema.WriteLine("DecimalSymbol=.");

            if (HasHeaders)
            {
                schema.WriteLine("ColNameHeader=True");
            }
            else
            {
                schema.WriteLine("ColNameHeader=False");
            }

            for (int i = 1; i <= this.SchemaColumnCount; i++)
            {
                schema.WriteLine("Col{0}=ColumnName{0} Text", i);
            }
        }
    }

    /// <summary>
    /// Initializes the data for importing.
    /// </summary>
    /// <param name="schema">
    /// The schema definition of the data to be imported.
    /// </param>
    /// <param name="filename">
    /// The filename of the data to import.
    /// </param>
    void InitialiseImport()
    {
        this.TempPath = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
        Directory.CreateDirectory(this.TempPath);
        this.WriteSchema();
        File.Copy(this.Filename, Path.Combine(this.TempPath, Path.GetFileName(this.Filename)));
    }

    /// <summary>
    /// Cleans temporary files after import completion.
    /// </summary>
    void Cleanup()
    {
        Directory.Delete(this.TempPath, true);
    }

    /// <summary>
    /// Imports data from the CSV file in order to create a set of entities.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="filename"></param>
    /// <param name="composer"></param>
    /// <returns></returns>
    public IEnumerable<T> Import<T>(Func<object[], T> composer)
    {
        this.InitialiseImport();
        try
        {
            using (IDbConnection dataConnection = new System.Data.OleDb.OleDbConnection(this.BuildConnectionString()))
            using (IDbCommand queryCommand = dataConnection.CreateCommand())
            {
                dataConnection.Open();
                queryCommand.CommandText = string.Format("select * from [{0}]", Path.GetFileName(this.Filename));
                using (IDataReader cursor = queryCommand.ExecuteReader())
                {
                    IList<T> entryList = new List<T>();
                    while (cursor.Read())
                    {
                        object[] values = new object[] { };
                        Array.Resize<object>(ref values, cursor.FieldCount);
                        cursor.GetValues(values);
                        entryList.Add(composer(values));
                    }
                    return entryList;
                }
            }
        }
        finally
        {
            this.Cleanup();
        }
    }
}

int index = 0;
CsvImportUtility csvutil = new CsvImportUtility();
csvutil.Filename = "myfile.csv";
csvutil.HasHeaders = false;
return csvutil.Import((values) => 
{
    return new RowItem() { No = index++, ColumnValue = values[0].ToString() }; 
}).ToList();