Geek asks and answers

Unable to read csv file correctly using Microsoft.Jet.OLEDB.4.0 provider

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

Below is the output after loading the file in the dataset. Output after loading the data 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;
}

Example csv file data:

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:

  • The first column data is converted from F01 to 1.
  • I also tried using Access 12.0 drivers.
+5
source share
1 answer

What you see are OLEDB drivers that try to provide you a service and infer the data types of your columns. In your case, it sees all “F01” in the first column and the numbers you want the numbers in this column to contain numerical data.

CSV, schema.ini , CSV. . :

[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

schema.ini , . . this.

schema.ini , , CSV.

OLEDB CSV, . CSV-, , , , , , OLEDB. .

+4

More articles:


All Articles