1

I am attempting to populate a DataGridView with data from a csv or comma delimited txt file the user selects. The csv gets loaded into the DataGridView, but in a certain column which contains a mixture of alpha or numeric values, if the first several values are numeric and then the data switches to alpha characters, they get dropped. See below:

Here I've imported a csv with a mix of alpha or numeric values in the cover column. The cells that should contain the alpha values are instead null.

enter image description here

Here I've imported a csv with only either null (the first value is supposed to be null) or alpha values. It has no issues.

enter image description here

It seems like perhaps there is some sort of data type guessing going on, where it thinks that the data should be numeric and nullfiies anything else.

Here's the code I'm using to import the CSV:

string conStr = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + Path.GetDirectoryName(loadPath) + ";Extensions=csv,txt";
OdbcConnection conn = new OdbcConnection(conStr);
OdbcDataAdapter da = new OdbcDataAdapter("Select * from [" + Path.GetFileName(loadPath) + "]", conn);
DataTable dt = new DataTable(loadPath);
da.Fill(dt);
csvTable.DataSource = dt;

Any help is appreciated.

bluefoot
  • 189
  • 2
  • 11
  • Does setting the `ValueType` property (http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridviewcolumn.valuetype.aspx) to `string` on the cell in question, before you bind the data source, make a difference? – Jim Mischel May 13 '13 at 18:37

1 Answers1

2

Have you considered using a generic CSV parser that will use the structure of the data to create a datatable?

This is an easy to use, generic parser, great for flat files like CSV files.

EDIT:

To expand upon Jims comment here is an example of using the TextFieldParser in C#. This only handles your first 3 fields, but should be enough of an example to see how it works.

String myFilePath = @"c:\test.csv";
DataTable dt = new DataTable();
dt.Columns.Add("HAB_CODE");
dt.Columns.Add("SIZE");
dt.Columns.Add("COVER");

using (var myCsvFile = new TextFieldParser(myFilePath)){
    myCsvFile.TextFieldType = FieldType.Delimited;
    myCsvFile.SetDelimiters(",");
    myCsvFile.CommentTokens = new[] { "HEADER", "COMMENT", "TRAILER" };

    while (!myCsvFile.EndOfData) {
        string[] fieldArray;
        try {
            fieldArray = myCsvFile.ReadFields();
            dt.Rows.Add(fieldArray);
        }
        catch (Microsoft.VisualBasic.FileIO.MalformedLineException ex) {
            // not a valid delimited line - log, terminate, or ignore
            continue;
        }
    // process values in fieldArray
    }    
}
Jake1164
  • 12,291
  • 6
  • 47
  • 64
  • Or use Microsoft.VisualBasic.FileIO.TextFieldParser, which works quite well. http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser.aspx – Jim Mischel May 13 '13 at 19:09
  • I agree, that's an example of a class that I think shouldn't be hidden in the VB library. – Jake1164 May 13 '13 at 19:48
  • I ended up going this route. It works quite well for me. As far as my problem, I found [this post](http://stackoverflow.com/questions/8683180/data-error-when-reading-csv-file-in-c-sharp-winforms?rq=1) that explains that the OdbcDataAdapter attempts to auto detect the column type. Indeed, my column was typed as Int32. – bluefoot May 13 '13 at 22:07