Reading an XLS file with the Microsoft Jet Engine

Question

Hi,

I want to allow my application to allow import of data through XLs files. I already do this with CSV files and XML files, but would like to open the scope for users. I am having trouble with the loading of the file. We load the files (XLS,CSV,Xml) into a data set and work on it from there. The loading code for XLS is below

        FileInfo fi = new FileInfo(filename);

        //create and open a connection with the supplied string
        OleDbConnection objOleDBConn;
        objOleDBConn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data  Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", fi.FullName));
        objOleDBConn.Open();

        DataTable dt = objOleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null || dt.Rows.Count == 0)
        {
            return;
        }

        string sheet = dt.Rows[0]["TABLE_NAME"].ToString();

        //then read the data as usual.
        OleDbDataAdapter objOleDBDa;
        objOleDBDa = new OleDbDataAdapter(string.Format("select * from [{0}]",sheet), objOleDBConn);
        objOleDBDa.Fill(data);
        objOleDBConn.Close();

So my data gets loaded ok, but it appears to set the data types of various columns, and this is a problem for one of my columns. Its a bit field and we have chosen to accept False, True, Yes,No, Y, N. There is code that transfers this into a bool later on. This works fine in a CSV file (for which the connection string is different) but in an xls if the first 10 rows are say "false" or "true", and then say the 11th says "yes", then i just get a null entry. I'm guessing that it reads the first few entries and determines the data type based on that? Is there a way to turns this off?

Thanks

Will

Answer 1

This question is very similar to http://stackoverflow.com/questions/898513/excel-cell-values-are-truncated-by-oledb-provider and http://stackoverflow.com/questions/1699483/excel-reading-in-asp-net-data-not-being-read-if-column-has-different-data-forma Looks like a couple of workable solutions are discussed in these other questions.

Answer 2

There is a registry setting to tell the Jet provider how many rows to read to infer the data type for the column. It defaults to 8 I believe. It is:

HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

(change version as applicable). In your case, it has infered boolean and therefore ignores the string value "yes".