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

Nate

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

http://www.dicks-blog.com/excel/2004...al_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" <naxtell at progeny dot net wrote in message
...
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.



"Dick Kusleika" wrote in message
...
Nate

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

http://www.dicks-blog.com/excel/2004...al_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

ok, I'm using a different driver:
connectstr = "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" &
FileName & ";"

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.

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?

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

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

"Dick Kusleika" wrote in message
...
Nate

ok, I'm using a different driver:
connectstr = "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ="
&
FileName & ";"

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.

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?

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

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" wrote in message
...
Nate

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

http://www.dicks-blog.com/excel/2004...al_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" <naxtell at progeny dot net wrote in message
...
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

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

"nate axtell" <naxtell at progeny dot net wrote in message
...
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


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.

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

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


"Dick Kusleika" wrote in message
...
Nate

"nate axtell" <naxtell at progeny dot net wrote in message
...
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


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.

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

"nate axtell" <naxtell at progeny dot net wrote in message
...
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.


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.

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