Solved

Not Reading from Excel with mixed types

Posted on 2010-08-24
3
566 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

820 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