When reading a CSV file using a DataReader and the OLEDB Jet data provider, how can I control column data types?

To expand on Marc's answer, I need to create a text file called Schema.ini and put it in the same directory as the CSV file. As well as column types, this file can specify the file format, date time format, regional settings, and the column names if they're not included in the file.

To make the example I gave in the question work, the Schema file should look like this:

[Data.csv]
ColNameHeader=True
Col1=House Text
Col2=Street Text
Col3=Town Text

I could also try this to make the data provider examine all the rows in the file before it tries to guess the data types:

[Data.csv]
ColNameHeader=true
MaxScanRows=0

In real life, my application imports data from files with dynamic names, so I have to create a Schema.ini file on the fly and write it to the same directory as the CSV file before I open my connection.

Further details can be found here - http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx - or by searching the MSDN Library for "Schema.ini file".

There's a schema file you can create that would tell ADO.NET how to interpret the CSV - in effect giving it a structure.

Try this: http://www.aspdotnetcodes.com/Importing_CSV_Database_Schema.ini.aspx

Or the most recent MS Documentation

Please check

http://kbcsv.codeplex.com/

using (var reader = new CsvReader("data.csv"))
{
    reader.ReadHeaderRecord();
    foreach (var record in reader.DataRecords)
    {
        var name = record["Name"];
        var age = record["Age"];
    }
}