data error when reading csv file in C# winforms

Question

I have a C# winforms that is reading a column from a csv file. It reads 3 of the 4 columns correct. The 4th column in the csv file is S4, but the dataset is displaying 4.

The code is:

string conn = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data"
            + "Source={0}; Extended Properties=""text;HDR=YES;FMT=DELIMITED""",
              strDirectoryPath);

OleDbConnection oleDBConn = new OleDbConnection(conn);
oleDBConn.Open();

OleDbDataAdapter da = new OleDbDataAdapter("Select * FROM [" + strFileName + "]",
                                           conn);
DataSet ds = new DataSet();
da.Fill(ds);

csv data sample is:

AA0013  Incident    Incident    S4
AA0016  Incident    Incident    S3
AA0017  Incident    Incident    S3
AA0023  Incident    Incident    S3
AA0076  Issue       Issue       S3
AA0079  Incident    Incident    S6
AA0082  Issue       Issue       S6
AA0084  Incident    Incident    S6
AA0085  Incident    Incident    S6

What would cause this and how can I resolve it?

Answer 1

The easiest way is parse the file manually without using database connection.

    string[] lines = File.ReadAllLines(path);

    foreach(string row in lines)
    {
        string[] data = row.Split(",");
        //Data processing goes here
    }

Notice that "," is data delimiter, you can use other delimiter such as " " or ";"

Answer 2

This is because some times OLEDB provider auto detect the data type of the column and try to convert all the values in that column to a specific data type it detects. to solve this problem you need to specify the schema.ini file that will hold information about each column and its data type so that OLEDB dont try to implicitly convert any column to its own favorite data type :)...

here is the complete guide.. http://www.aspdotnetcodes.com/Importing_CSV_Database_Schema.ini.aspx

Regards.