Not able to read csv file properly using Microsoft.Jet.OLEDB.4.0 provider

Question

I am trying to read a csv file using following code. The file has columns headers also.

Following is the output after loading the file in dataset.

public DataSet LoadCVS(string filePath)
{
    DataSet ds = new DataSet();
    string fileName = System.IO.Path.GetFileName(filePath);
    try
    {
        string path = @System.IO.Path.GetDirectoryName(filePath);

        using (OleDbConnection conn =
            new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + @";Extended Properties=""Text;HDR=Yes;FMT=Delimited"""))
        {
            using (OleDbCommand cmd =
                new OleDbCommand("SELECT * FROM [" + fileName + "]", conn))
            {
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);

                da.Fill(ds, "csv");
            }
        }
    }
    catch (Exception ex) //Error
    {
        MessageBox.Show(ex.Message);
    }
    return ds;
}

Sample data of csv file:

Org,Item Number,Item Description,Lot Number,Lot Expiration Date,Marketing Division,Product Type
F01,Jan-00,LFIT MORSE TAPER HEAD,MHD7D8,3-May-14,5,5
F01,Jan-05,LFIT MORSE TAPER HEAD,MHATY9,1-Mar-14,5,5
F01,Jan-00,LFIT MORSE TAPER HEAD,MHDEN1,8-Mar-14,5,5
F01,Jan-05,LFIT MORSE TAPER HEAD,MHNY4L,18-Nov-14,5,5
F01,Jan-10,LFIT MORSE TAPER HEAD,MHHLYR,31-May-14,5,5
F01,Jan-00,LFIT MORSE TAPER HEAD,MJNKRK,10-Oct-15,5,5
F01,Jan-00,LFIT MORSE TAPER HEAD,MKNN38,14-Nov-16,5,5
F01,Jan-05,LFIT MORSE TAPER HEAD,MJDV6X,8-Apr-15,5,5
F01,Jan-05,LFIT MORSE TAPER HEAD,MKAK94,22-Feb-16,5,5

Problem:

• First column data is converted from F01 to 1.
• I have tried with Access 12.0 drivers also.

Answer 1

What you're seeing are the OLEDB drivers trying to do you a favor and infer the data types of your columns. In your case it sees all the 'F01' s in the first column and figures you want numbers in that column to hold numeric data.

You can specify the data types of your CSV columns by creating a text file named schema.ini in the same folder as your CSV. In this file you'll specify for each column in your data file the column's name and the column's data type. Here's an example for your data:

[NameOfYourCSVDataFile.csv]
Col1=Org Text
Col2="Item Number" Text
Col3="Item Description" Text
Col4="Lot Number"  Text
Col5="Lot Expiration Date" Text
Col6="Marketing Division" Text
Col7="Product Type" Text

There are other properties you can define in the schema.ini file. For more details see this.

If you can't or don't want to create the schema.ini by hand you can create the file programmatically, before you attempt to read your CSVs.

I highly recommend not using OLEDB to read CSVs if you can at all avoid it. There are a number of highly capable CSV readers out there and if the sample data you provided in your question is at all like your real data, then I bet you'll find any of these easier to use than OLEDB. Your question brought back some painful memories.