Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Not Reading from Excel with mixed types

Posted on 2010-08-24
3
Medium Priority
?
601 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

916 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