asked on

Not Reading from Excel with mixed types

Hi!
I'm t trying to import some data from Excel by an OLEDB command string to a dataTable in c#.
I have one column with mixed types, most of the fields are integers and some are strings.

I set the ImportMixedTypes = Text and TypeGuessRows = 0
If one string data is on the first fields of the column, the type is text and no data is missing.
So, if the string data is at the end of the column, the type is float and all the strings data are missing.

Per example, my column contains about 10,000 integers and 200 strings at the end.
I can't change the format of the Excel sheet.

My connection string is:
string cnx = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\doc.xls;
Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;
ImportMixedTypes=Text;TypeGuessRows=0\"";

My OledbCommand is:
"SELECT [C1] ,[C2] FROM [Sheet1$2:11313] ";

I need to find a way to set the ImportMixedTypes = Text for any Excel sheet.

Thanks

calacuccia

I'lm afraid you'll have to accept it or find a workaround through some intermediate Excel file, if I understand the article below well

From http://support.microsoft.com/kb/257819

Considerations That Apply to Both OLE DB Providers
A Caution about Mixed Data Types

As stated previously, ADO must guess at the data type for each column in your Excel worksheet or range. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.

For example:
In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.
As a result, if your column contains mixed values, your only recourse is to store numeric values in that column as text, and to convert them back to numbers when needed in the client application by using the Visual Basic VAL function or an equivalent.

To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base:
194124 (http://support.microsoft.com/kb/194124/EN-US/ ) PRB: Excel Values Returned as NULL Using DAO OpenRecordset

Albator666

ASKER

By default, Excel scans the 8 first rows to find the data type.

If I set the TypeGuessRows = 0, Excel appears to use the first 25 rows to set the type.

So, I need to find a way where Excel get all the rows (more than 25) to find the type (or mixed type).

ASKER CERTIFIED SOLUTION

Albator666

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial