Solved

Not Reading from Excel with mixed types

Posted on 2010-08-24
3
548 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:Albator666
  • 2
3 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 33514403
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
0
 

Author Comment

by:Albator666
ID: 33562058

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).
0
 

Accepted Solution

by:
Albator666 earned 0 total points
ID: 33562319

I solved my problem.

Just to set the TypeGuessRows manually.

Open the register folder : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
and set the TypeGuessRows = 0

I don't know why the TypeGuessRows = 0 don't works in the connection string, but it work in this way.





0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now