4

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?

Jason Down
  • 21,731
  • 12
  • 83
  • 117
user1111955
  • 459
  • 7
  • 19
  • 3
    Can you provide a few sample lines of the CSV? – user1231231412 Dec 30 '11 at 19:27
  • Here's an article talking about configuring the schema.ini file to use the OleDb stuff with CSV, specifically the delimiters. http://weblogs.asp.net/fmarguerie/archive/2003/10/01/29964.aspx – user1231231412 Dec 30 '11 at 19:34
  • I am pretty certain something like this should not require a registry modification. Especially since the first 3 rows are being retreived accurately – user1111955 Dec 30 '11 at 19:39
  • 1
    totally agree. I actually meant this link http://msdn.microsoft.com/en-us/library/ms709353.aspx inside that article talking about configuring it. Which may not help since it IS reading the 1st few lines correctly, just something to check into. – user1231231412 Dec 30 '11 at 19:41
  • 1
    That data does not look like CSV. Is this the actual data that you are using or are you really looking to process a fixed file format in a file that happens to have a csv extension? – competent_tech Dec 30 '11 at 19:42
  • Check this: http://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data/5721521#5721521 – Wouter de Kort Dec 30 '11 at 19:42
  • @competent_tech it's a file with a csv extension (former xls file) – user1111955 Dec 30 '11 at 19:45
  • @user1111955: I think that you need to process it as a fixed column file. Change the fmt to fixed and use the information from MSDN: http://msdn.microsoft.com/en-us/library/ms709353.aspx. – competent_tech Dec 30 '11 at 19:47
  • @competent_tech I just tried FixedLength.... does not work either. – user1111955 Dec 30 '11 at 19:51

2 Answers2

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.

Shoaib Shaikh
  • 4,565
  • 1
  • 27
  • 35
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 ";"

Niyoko
  • 7,512
  • 4
  • 32
  • 59