Data error while reading csv file in c # winforms

Question

I have c # winforms that reads a column from a csv file. It reads 3 of 4 columns correctly. The 4th column in the csv S4 file, but 4 displayed in the dataset.

Code:

 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); 

Example csv data:

 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 can cause this and how can I solve it?

Answer 1

This is because the OLEDB provider automatically detects the column data type and tries to convert all the values ​​in that column to the specific data type that it detects. To solve this problem, you need to specify the schema.ini file, which will store information about each column and its data type so that OLEDB does not implicitly try to convert any column into its own favorite data type:) ...

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

Sincerely.

Answer 2

The easiest way is to analyze the file manually without using a database connection.

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

Please note that "," is a data separator, you can use another separator, such as "or"; "