DataReader has wrong schema/data types RRS feed

  • Question

  • We're transforming a CSV file to XML using the OleDbDataReader.  When we're getting the values for each row, some of the values are missing because the wrong data type is given to the column.  Specifically, a column is being seen as a decimal type, but it should be a string.

    We cannot enforce a schema because we can have one or more optional columns and the data may or may not be required in the fields.

    Is there any way I can force everything to be a string?  Everything just gets cast into it's correct object type once it's been read anyways.

    An alternative option to this is to force all text columns to have quotes around them, which could cause inconvenience for the users and a difference from the first version of our software.

    • Moved by Mark.Ashton - MSFT Tuesday, March 10, 2009 11:50 PM OleDbDataReader (Moved from ADO.NET DataSet to ADO.NET Data Providers)
    Tuesday, March 10, 2009 8:57 PM

All replies

  • Are you using Jet OLEDB provider to read CSV? If you do then you probably using schema.ini file to describe data types of the columns. Setting types to string inside of schema.ini file would do this for you. Another option is to try to use IMAX=1 property setting inside of Extended Properties part of your connection string. IMAX=1 enforces Jet to treat all the values as strings. It works when you query data from Excel, but I have never tried to use this option with CSV or any other file.

    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, March 11, 2009 9:50 AM