Solved

Not Reading from Excel with mixed types

Posted on 2010-08-24
3
537 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
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.

757 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