MSDASQL and ImportMixedTypes
MarcG
and IMEX, but still can't manage to get all of the cells loaded in a mixed
(mostly numeric) column.
My connection string is:
Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};Extended
Properties="ImportMixedTypes=Text;Excel
8.0;HDR=Yes;IMEX=1";DBQ=C:\Ntt\MTC-WebApp\MTC\Daily.xls
The effect is the same if the ImportMixedTypes=Text is moved out of the
Extended Properties parameter. Curiously, it doesn't seem to care if I say
ImportMixedTypes=Textasdf
code is as follows:
OdbcConnection conn = new OdbcConnection(conStr);
string selectString =
System.Web.Configuration.WebConfigurationManager.AppSettings[MTC.MACHINE_SELECT_COMMAND].ToString();
OdbcDataAdapter adapter = new OdbcDataAdapter(selectString, conn);
OnFloorMachinesDataTable ofmTable = new
OnFloorMachinesDataTable();
ofmTable.TableName = "ofmTable";
DataTable newTable = adapter.FillSchema(ofmTable,
SchemaType.Source);
adapter.Fill(ofmTable);
It seems that doing the FillSchema is irrelevant. OnFloorMachinesDataTable
is designed in an XSD and has some columns, none of which match the
spreadsheet.
The spreadsheet has two sheets, and I'm after the first one. The select
statement reads:
Select * from [Daily_Priority$]
After the Fill, the sheets columns have been added to the table and all is
well, except that the text cells are empty (DBNull).
Thx
Marc
WenYuan Wang [MSFT]
According to your description, you face an issue that the text cells are
empty when using OdbcDataAdapter to retrieve data from Excel. Please
correct me if I misunderstood anything.
Have you checked the registry key on your machine?
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
The key "ImportMixedTypes=Text" means the mixed data will be converted to
text.
By default, the driver only looks at the first EIGHT rows to determine the
datatypes. Therefore, if the first 8 rows are all Numeric, the driver will
recognize the datatype of current column as Numeric. Thus, the text cell in
this column will be lost.
To avoid this issue, what we should do is to increase the number of
"TypeGuessRows" key. Then, drive can scan enough rows to determine the
correct datatype.
You may check the following document for detail information.
http://support.microsoft.com/kb/194124/en-us
[PRB: Excel Values Returned as NULL Using DAO OpenRecordset]
Hope this helps. Please try the above method and let me know the result.
I'll follow up. We are glad to assist you.
Have a great day,
Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
MarcG
From the KB article...
Set Db = OpenDatabase("C:\Temp\Book1.xls", _
False, True, "Excel 8.0; HDR=NO; IMEX=1;")
NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the
registry setting ImportMixedTypes=Text will be noticed. This forces mixed
data to be converted to text. For this to work reliably, you may also have to
modify the registry setting, TypeGuessRows=8. The ISAM driver by default
looks at the first eight rows and from that sampling determines the datatype.
If this eight row sampling is all numeric, then setting IMEX=1 will not
convert the default datatype to Text; it will remain numeric.
Now, in my connection string, I specify IMEX=1 and ImportMixedTypes=Text.
In the registry, ImportMixedTypes=Text as well and there is no IMEX entry.
I'm wondering if the problem is how I'm specifying the information in the
connections string itself. The sample in the KB article is programatic, but I
am specifying everything in the text of the string including
Extended Properties="ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1"
Is this the correct way to do this? The full string is in my last post.
It happens that in my spreadsheet, there is a header row and the first data
row in the column in question is non-numeric (almost all of the remaining
rows are numeric). As a test, I added TypeGuessRows=1 so it should only read
the first row, which is the one that has the text entry. The result was the
same: the column was set to System.Double anyway.
This is why I'm wondering if the whole "Extended Properties" parameter is
being ignored. As noted earlier, if I say ImportMixedTypes=Textasdf I get no
error message when the connection string is used.
Also, as an experiment, I went in after the FillSchema and set the type of
the column to String before doing the Fill, but the cell value was still
DBNull.
Marc
WenYuan Wang [MSFT]
Thanks for your reply.
I reviewed your code again and reproduced the issue on my side.
I noticed your web application using OdbcConnection rather than
OleDBconnection. I'm afraid the key word "Extended Properties" is not
supported in ODBC Connect string. It is only available in OleDB Connect
string.
Therefore, the OdbcConnection will ignore the Extened Property (IMEX=1).
The text cells are empty in your application. Changing the schema of
DataTable doesn't help in this case. It is the because that the drive ( not
net application) eats the data.
The way to resolve this issue is using OleDb.
Would you please try the OleDbConnection (if it is possible for you)?
ocn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Ntt\MTC-WebApp\MTC\Daily.xls;Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1""";
Hope this helps. Please try the OleDbConnection (if it is possible for you)
and let me know the result. We are glad to assist you.
MarcG
> ocn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\Ntt\MTC-WebApp\MTC\Daily.xls;Extended Properties=""Excel
> 8.0;HDR=Yes;IMEX=1""";
This produces the "Cannot find installable ISAM" message. There's a lot of
references to this on the net, but KB271908 points out that Jet was no longer
distributed with MDAC as of MDAC2.6.
I could get a JET service pack and install it, but it just means that I'm
building a new app that depends on obsolete MS products.
Sigh...
I think I'll generate a text version of the spreadsheet, parse the damn
thing myself and build the table based on what I find.
Marc
WenYuan Wang [MSFT]
Thanks for your reply.
I consulted product team.
The document which you found gives a misleading impression.
Jet 4.0 is installed with Windows 2000 and later, including Vista, so it
will follow the lifetime of the OS through 2017 or so and even longer if it
ships with Longhorn. Therefore, this components has been removed from
Microsoft Data Access Components (MDAC) (2.6 and latter).
We need not to worry about this technology becomes obsolete.
Hope this helps, please don't hesitate to let me know if you have any more
concern. We are glad to assist you.
MarcG
Sorry - still confused. I think that you are saying that Jet is part of the
base OS for Win2K, XP, and Vista, and, because it is in the base, it is no
longer part of MDAC.
If this is correct, why am I getting the "Cannot find installable ISAM"
message? Following KB 239114, I checked and I do have an XP/SP2 version of
Msjet40.dll in System32 (along with a Msjet35.dll). System32/dllcache
contains Msjet40.dll only.
Marc
WenYuan Wang [MSFT]
Thanks for your reply.
You understood my reply correctly. Jet is part of OS for windows 2000 and
later. Therefore, it will be removed from MDAC (2.6 and later)
According to you description, your application is running against Windows
XP sp2. Jet component has already been installed your system.
Now, in order to clear the issue, I think what you concern is why getting
the error message ""Cannot find installable ISAM" in OleDBConnection.
Please correct me if I misunderstood anything here.
In general, we may have a syntax problem with the connection string which
will cause this particular error to occur.
Please make sure your connection string is correct.
For you case it should be (just as what we discussed in the previous reply)
valid:
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Ntt\MTC-WebApp\MTC\Daily.xls;Extended Properties="Excel
8.0;HDR=Yes;IMEX=1"
Note: we cannot define "ImportMixedTypes=Text;" in the connection string.
This property will be loaded from Register.
Invalid:
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Ntt\MTC-WebApp\MTC\Daily.xls;Extended
Properties="ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1"
If the issue still persists, please let me know the exact connection
string. I would have to review it and perform further analyze. If there is
anything unclear, please also feel free to let me know. We are glad to
Asad
I am still havnig same issue i ahve installed ISAM already and WinXP sp2 but
unable to read alphanumeric values from excel sheet can you please help me in
this regard? My connection string is as follows
string strExcelConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + saveLocation + ";Extended Properties=ImportMixedTypes=Text;Excel
8.0;HDR=Yes;IMEX=1";
Thanks
Asad