adodb.recordset with excel

  • Thread starter Thread starter nate axtell
  • Start date Start date
N

nate axtell

I'm using an adodb.recordset to query and obtain the contents of an Excel
file in ASP with VBScript.
I'm having a problem when there are numeric entries mixed with alpha &
numeric entries within the same column.
The query I use seems to pick one of the two types and return NULL for any
of the other type.
Is there a way to specify that the columns will all be of a specific type?
I've tried to set the format in the Excel file to all General or all Text
but that doesn't seem to help. Any ideas would help, thanks.
Here's some code of what i'm doing basically:
....
Set XLconn = CreateObject("ADODB.Connection") 'Excel connection object
XLconn.ConnectionString = connectstr
XLconn.Open
Set XLrs = CreateObject("ADODB.Recordset") 'create the record set object
XLrs.cursortype = 3
XLrs.open "Select * From [sheet1$]", XLconn
....
Thanks,
Nate
 
ok, I'm using a different driver:
connectstr = "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" &
FileName & ";"
But, from the link you gave talks about registry settings, which I can not
touch since this app will be used from client machines.
 
Nate
ok, I'm using a different driver:
connectstr = "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" &
FileName & ";"
Click to expand...

I think the theory is the same regardless of the driver. It would just be
in a different place in the registry. I'm just guessing though.
But, from the link you gave talks about registry settings, which I can not
touch since this app will be used from client machines.
Click to expand...

That's going to make it tough, I think. The only other way that I can think
of is to open the source workbook and insert some rows with data of a
particular type. If you can open the workbook, though, you can just copy
the range over and not use ADO. Any chance you can just open the workbook?
 
Thanks again for the link, Dick.
I was checking out the registry for ImortMixedTypes and I found mine set to
"Text".
The link you posted says "IMEX=0 and IMEX=2 result in ImportMixedTypes being
ignored and the default value of 'Majority Types' is used. IMEX=1 is the
only way to ensure ImportMixedTypes=Text is honored"...

Do you know if this means that the ImortMixedTypes key is supposed to be
"Majority Types" by default or that that is just a default that is used by
the driver? I am worried that ImportMixedTypes wont be "Text" for every
clients' installation by the wording of the qoute above. If it is always
Text then I should be fine.

Thanks, Nate



Dick Kusleika said:
Nate

Here's everything you ever wanted to know about mixed data types

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Thanks to OneDayWhen for this awesome contribution.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

nate axtell said:
I'm using an adodb.recordset to query and obtain the contents of an Excel
file in ASP with VBScript.
I'm having a problem when there are numeric entries mixed with alpha &
numeric entries within the same column.
The query I use seems to pick one of the two types and return NULL for any
of the other type.
Is there a way to specify that the columns will all be of a specific type?
I've tried to set the format in the Excel file to all General or all Text
but that doesn't seem to help. Any ideas would help, thanks.
Here's some code of what i'm doing basically:
...
Set XLconn = CreateObject("ADODB.Connection") 'Excel connection object
XLconn.ConnectionString = connectstr
XLconn.Open
Set XLrs = CreateObject("ADODB.Recordset") 'create the record set object
XLrs.cursortype = 3
XLrs.open "Select * From [sheet1$]", XLconn
...
Thanks,
Nate
Click to expand...
Click to expand...
 
No, a workbook is considered an unsafe activex control i thought, which we
have prompt the user everytime one is found (IE setting). So we wouldn't
want to go with that. I just posted a message before this about the
registry key already being what I want it to be.
Nate
 
Another concern,
I have tried IMEX=1 with the Jet driver and I DO see the results I want,
Number fields and Text fields are being returned together.
but in the case where a number is quite long I see 9911220193 in the excel
file but when it gets read into the recordset and then passed over to SQL
server I see "9.91122E+009". This is quite strange. Any idea why this
scientific notation is showing up?
Thanks again,
Nate
 
nate axtell said:
Thanks again for the link, Dick.
I was checking out the registry for ImortMixedTypes and I found mine set to
"Text".
The link you posted says "IMEX=0 and IMEX=2 result in ImportMixedTypes being
ignored and the default value of 'Majority Types' is used. IMEX=1 is the
only way to ensure ImportMixedTypes=Text is honored"...

Do you know if this means that the ImortMixedTypes key is supposed to be
"Majority Types" by default or that that is just a default that is used by
the driver? I am worried that ImportMixedTypes wont be "Text" for every
clients' installation by the wording of the qoute above. If it is always
Text then I should be fine.
Click to expand...

The way I understand it is that ImportMixedTypes is "Text" by default. But
IMEX is NOT 1 by default, so ImportMixedTypes is ignored - by default. It
would follow then that if you declare IMEX in your extended properties AND
the user has not changed their default, then it will work. I think that
"the default value of 'Majority Types' is used" means that IMEX <> 1 by
default and Majority Types is the default for an IMEX <> 1, not that
Majority Types is the default for that registry key.

Whether you want to rely on user's default not being changed is up to you.
On my Win98SE, it's "Text" and I know I've never changed it.
 
Nate

nate axtell said:
Another concern,
I have tried IMEX=1 with the Jet driver and I DO see the results I want,
Number fields and Text fields are being returned together.
but in the case where a number is quite long I see 9911220193 in the excel
file but when it gets read into the recordset and then passed over to SQL
server I see "9.91122E+009". This is quite strange. Any idea why this
scientific notation is showing up?
Thanks again,
Nate
Click to expand...

Pure guessing here. I don't know much about SQL server, but it almost looks
like it's converting the number to fits its own data type. Like if that
field was set for Integer and needed to be set for Long Integer.
 
I've done some testing and it happens before SQL Server gets its hands on
it.
It happens when the rows get put into the recordset. I can't see the
recordset changing it to scientific notation so I'm going to assume that
Excel is converting it over to Sci. Not. before it gets brought over as a
string.
Any ideas about how and why Excel would return the value in Sci. Not.?
thanks,
Nate
 
Nate

That's odd. When .CopyFromRecordset into another workbook, the number comes
through fine. Also, when I loop through the records and fields and print
the .Value property to the Immediate Window, it comes through fine.

What makes you think Excel is doing it?
 
Dick,
The only place I've seen this occur is when I resize a column in Excel.
Excel will then
show a Sci. Not. version of the number because of the reduced space. When I
loop
through the recordset and then display the row I see the Sci. Not.:

XLrs.MoveFirst
while not XLrs.eof
Dim k: k = 0
Dim strT, ColumnField
strT = ""
For each ColumnField in XLrs.Fields
strT = strT & XLrs(SelectionValue(k)).value & " , "
k = k + 1
next
msgbox strT
XLrs.movenext
wend

Unless the recordset is changing the number, which I have never seen before,
I would
say that Excel is passing over the Sci. Not. version of the number which the
recordset
is then converting to text instead of translating back to its normal number
representation.

Note that I still have "Number" fields in my Excel file; there are still
mixed text and
numbers. If I select the Sheet contents and change the format to text this
only changes
the way numbers appear in their cells, not what they actually are. If I
then double-click
on a cell then the number will be saved as text. I even confirmed this when
I found the
following article: http://support.microsoft.com/?id=194124 (see resolution
1).
If I do double-click on the 'Long' number field and click away, the number
will be saved
as text. Then from here the number will show up in its normal format
because Excel thinks
of it as a text cell (these cells show a green triangle in the upper left
corner).

Because of this I think that Excel is the culprit. The recordset is just an
ADODB.Recordset,
nothing special that would convert numbers to sci. not. The value received
depends on
the data format in Excel.

I wonder if this has ever been seen before. I'm still trying to figure out
what to do with
TypeGuessRows in the registry since I can't edit the registry to set it to
0. I just know
someone will come up with the case where they have the first eight rows as
numbers
and then they will throw a text field in somewhere after that. The IMEX was
a big help
though.
Thanks,
Nate
 
nate axtell said:
I've done some testing and it happens before SQL Server gets its hands on
it.
It happens when the rows get put into the recordset. I can't see the
recordset changing it to scientific notation so I'm going to assume that
Excel is converting it over to Sci. Not. before it gets brought over as a
string.
Any ideas about how and why Excel would return the value in Sci. Not.?
thanks,
Nate

XLrs.MoveFirst
while not XLrs.eof
Dim k: k = 0
Dim strT, ColumnField
strT = ""
For each ColumnField in XLrs.Fields
strT = strT & XLrs(SelectionValue(k)).value & " , "
k = k + 1
next
msgbox strT
XLrs.movenext
wend
Click to expand...

Here's my test

Dim sMyStr As String

Do While Not oRs.EOF
For i = 0 To oRs.Fields.Count - 1
sMyStr = sMyStr & oRs.Fields(i).Value & ", "
Next i
MsgBox sMyStr
oRs.MoveNext
sMyStr = ""
Loop

and the 10 digit number shows up fine. I wonder what the difference could
be.
XL2000
Win98SE
MS ADO 2.7
 
Let's see if we can clarify things.

The registry values for a default Jet installation are:

ImportMixedTypes=Text
TypeGuessRows=8

Not many users will know of their existence, even MS Access users
being the primary users of Jet. The prevailing advice from MS Access
MVPs is to work with the registry settings rather than amend them to
suit. So I think the above default values may be pretty much assumed.

Now let's talk about the values Jet uses at run-time. The run-time
value of ImportMixedTypes defaults to 'Majority Type' i.e. the
registry key is read and validated but not actually used. If IMEX=1 is
explicitly specified, the registry key is used and, as stated above,
it is relatively safe to assume ImportMixedTypes will be 'Text'.

Let's use an example. The Excel column contains the following data:

1
2
Hello
World
9911220193

Assume the registry values for a default Jet installation apply and no
extended properties specified in the connection string (except 'Excel
8.0' <g>). All rows are scanned, a mixed types situation is detected,
the registry key is not used and instead Majority Type will prevail,
the majority type is determined as being adDouble, the text values
cannot be coerced as adDouble so will be returned as null, the
resulting recordset will be:

1
2
<<null>>
<<null>>
9911220193

This time used IMEX=1 in the connection string. All rows are scanned,
a mixed types situation is detected, the registry key is honored and
'Text' will be used, the field data type will be adVarWChar, all
values present can be coerced, the resulting recordset will be:

1
2
Hello
World
9911220193

Where all values are strings. Looking more closely at that last value
in the Immediate Window:

m_rsMain.MoveLast

? m_rsMain(0).Type = adVarWChar
True

? TypeName(m_rsMain(0).Value)
String

? m_rsMain(0).Value
9911220193

So I'm not getting the same behavior as the OP i.e. no scientific
notation for me, as I would expect unless there was more than 15
significant figures or I'd formatted the cell/column as scientific.
When I do format the cell with Excel's default scientific format, the
resulting string is 9.91E+09 and I'd guess this is the result *if*
Excel was doing some implicit coercion. I can reproduce by formatting
the cell with the custom 0.00000E+000 format. I'm now wondering if the
OP's cell has a similar custom format.

I knew that Jet used both the cell's value and format to determine
data type; I think it is more correct to say Jet always uses a cell's
formatted value (in Excel VBA terms, the Range's Text property rather
than its Value property).

Jamie.

--
 
The IMEX works as designed, and unfortunately I have to live with the
TypeGuessRows=8, just warn users ahead of time so they watch for it. If the
first 8 rows are numbers for a column IMEX=1 has no affect... nothing can be
done about that. Now, I reproduce the error with the following:
1. Take a clean cell in Excel and change its format to number
2. type in a number
3. change its format to text.

Now i read it into the recordset and when ever i see a E- or E+ I print out
the following:
'rqCol is a column index...
msgbox XLrs.fields.item(rqCol).type
msgbox typename(XLrs.fields.item(rqCol).value)

I see 202 (or adVarWChar) and "String" in the msgboxes.
There is NO special formatting that is applied to the cells. I think I'm
just going to catch it and warn the user of the E- or E+ and let them choose
to continue if it is a legit string.
 
Back
Top