Reading an XLS file with the Microsoft Jet Engine

Question

I want to allow my application to import data from 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 loading 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. It's a bit field and we have chosen to accept False, True, Yes, No, Y, and N. There is code that transfers this into a boolean 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?

Question: Is there a way to turn off the mechanism that identifies a column's data type based on the first few entries?

You could try adding MaxScanRows=1 to the extended properties of the connection string, and then have text fields in the first row of the sheet. I have never tried this but it might work?? — Tester101, Nov 25 '09 at 13:08

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

This question is very similar to Excel cell-values are truncated by OLEDB-provider and Excel reading in ASP.NET : Data not being read if column has different data formats Looks like a couple of workable solutions are discussed in these other questions.

edited May 23 '17 at 11:48

Community

1
1

answered Nov 25 '09 at 12:53

Joe Barone

3,112
3
24
20

Thanks but it doesnt quite offer the complete solution, or not the one i was looking for. Changing the registry might not work if the users security policy had locked it down right? – wdhough Nov 26 '09 at 10:36

score 0 · Answer 2 · answered Nov 25 '09 at 12:53

0

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".

answered Nov 25 '09 at 12:53

David M

71,481
13
158
186

Thanks but what happens if you dont have access to the registry because the security policy of that user is locked down. I cant believe that microsoft would write a feature and in order to turn it off you have to set the feature to test everything just to find out that you dont want to use the feature, seems backwards. Thanks Will – wdhough Nov 26 '09 at 10:35
Short of splitting the sheet into lots of 8 row sheets, not much you can do then. – David M Nov 26 '09 at 12:05

score 0 · Answer 3 · answered May 16 '18 at 12:45

trick is to include header line as row from which to infer data type, so that all columns will be read as string. Then you will be able to parse in code to correct data type, if you need, without losing values - use for this HDR=No

objOleDBConn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data  Source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", fi.FullName));

score 0 · Answer 4 · edited May 23 '17 at 12:31

Try this OleDBAdapter Excel QA I posted via stack overflow.

I populated a worksheet column w/ all TRUE or FALSE and then threw in several "yes" or "no" values at random and it worked fine...

Run in Debug mode, then click on the DataSet Visualizer after it's populated to see results. Or, add this to the end of the code for the output

// DataSet:          
Object row11Col3 = ds.Tables["xlsImport"].Rows[11][3];
string rowElevenColumn3 = row11Col3.ToString();

Reading an XLS file with the Microsoft Jet Engine

4 Answers4

Linked