nulls in a data set populated via a csv file

  • Thread starter Thread starter Paul T
  • Start date Start date
P

Paul T

Hi,

I am populating a dataset using ado.net and the data source is a csv file.

Here is some example data:

row 1, 3/1/2004, status, 0, 1, 2, 3, 4, 5, 6, 7...
row 2, 3/1/2004, status, 0, 1, 2, 3, 4, 5, 6, 7...
row 3, 3/1/2004, status, 0, 1, 2, 3, 4, 5, 6, 7...
row 4, 3/1/2004, status, 0, , X, 3, 4, 5, 6, 7

There are rules that state how i have to handle null values (the empty value
in row 4) and 'bad data' values (the X in row 4, that is supposed to be
numeric). When I populate the dataset, I guess ADO is making some guesses
what the data type of the column is based on some sample values. My problem
is that when I go to process row 4, both values for the null and the
character are assigned values system.dbnull - therefore I cannot
differentiate any kind of code for the 2 - they both look like nulls coming
out of the dataset. Any suggestions how to tackle this problem?

Thanks in advance.
 
¤ Hi,
¤
¤ I am populating a dataset using ado.net and the data source is a csv file.
¤
¤ Here is some example data:
¤
¤ row 1, 3/1/2004, status, 0, 1, 2, 3, 4, 5, 6, 7...
¤ row 2, 3/1/2004, status, 0, 1, 2, 3, 4, 5, 6, 7...
¤ row 3, 3/1/2004, status, 0, 1, 2, 3, 4, 5, 6, 7...
¤ row 4, 3/1/2004, status, 0, , X, 3, 4, 5, 6, 7
¤
¤ There are rules that state how i have to handle null values (the empty value
¤ in row 4) and 'bad data' values (the X in row 4, that is supposed to be
¤ numeric). When I populate the dataset, I guess ADO is making some guesses
¤ what the data type of the column is based on some sample values. My problem
¤ is that when I go to process row 4, both values for the null and the
¤ character are assigned values system.dbnull - therefore I cannot
¤ differentiate any kind of code for the 2 - they both look like nulls coming
¤ out of the dataset. Any suggestions how to tackle this problem?
¤

You may need to define the schema using a schema.ini file:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Just remember that a column with both numeric and alphabetic values is alphanumeric and you will
need to define it as such. Otherwise you end up with the nulled out alpha values in a numeric field
(as you appear to be experiencing).


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top