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?

Answer 1

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.

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:

HKLMSoftwareMicrosoftOffice12.0Access Connectivity EngineEnginesExcelTypeGuessRows

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

Answer 3

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();

Answer 4

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));

友情链接