ADO reading Excel data

[Unnur U]

Dear support,

I must read Excel data into a ADO recordset in Access.
All works fine if all IDs are numeric or string. If IDs
are sometimes string and sometimes numeric does ADO
returns Null value for the IDs which are not of same
datatype as the first value.

Following is very simple example of the problem.

The Excel file Test.xls is as following (a header and 4
lines of data):

ID Descript. Unit Price
1 ProA m 123
A ProB ft 345
2 ProC kg 567
B ProD mm 789

My VBA code is as following:

Dim cnnExcel As New ADODB.Connection
With cnnExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Test.xls"
End With

Dim rsExcel As New ADODB.Recordset
rsExcel.Open "Select * from [Sheet1$]", cnnExcel,
adOpenStatic

Do Until rsExcel.EOF
Debug.Print rsExcel.Fields(0).Value
Debug.Print rsExcel.Fields(1).Value
Debug.Print rsExcel.Fields(2).Value
Debug.Print rsExcel.Fields(3).Value
rsExcel.MoveNext
Loop

Do you know how I can solve this?

Unnur

 

[GVaught]

The data type for a column of values has to be the same for all data. You
can't have numbers mixed in with text values. Designate the numbers in Excel
as Text values by placing a '1 in front so Access will recognize the number
type data as text.

 

[onedaywhen]

Have a read of this:

In theory, specifying MAXSCANROWS=0 in the Extended Properties of the
connection string should force ADO to scan all rows before choosing a
data type. But, as experienced by myself and many other users,
changing the MAXSCANROWS value to 0 does nothing. Finally, I found
that you can set the value in the registry by changing the
TypeGuessRows value to any number from 0 to 16. Setting the value to 0
(zero) will force ADO to scan all column values before choosing the
appropriate data type. Below is the location to the key value:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

Another thing to consider is to use a query that creates a new temp
table in your MS Access db using the Excel data and then manipulating
the data from here. The query would be something like:

SELECT *
INTO MyTempTable
FROM [Excel 8.0;Database=C:\Test.xls].[Sheet1]

 

[Sheila Barber]

I don't understand what you are saying about the data
formats. I have numbers and alpha in my first field,
example: 16780 & KWOS-5LGFPT-T. I told excel to see the
field as text. But when I link the table to a database, I
get #NUM errors where the numbers are. And if I sort my
data so the numbers are listed before the alpha, I get the
#NUM error where the alpha entries are. Any idea why?

Sheila

-----Original Message-----
The data type for a column of values has to be the same for all data. You
can't have numbers mixed in with text values. Designate the numbers in Excel
as Text values by placing a '1 in front so Access will recognize the number
type data as text.

Dear support,

I must read Excel data into a ADO recordset in Access.
All works fine if all IDs are numeric or string. If IDs
are sometimes string and sometimes numeric does ADO
returns Null value for the IDs which are not of same
datatype as the first value.

Following is very simple example of the problem.

The Excel file Test.xls is as following (a header and 4
lines of data):

ID Descript. Unit Price
1 ProA m 123
A ProB ft 345
2 ProC kg 567
B ProD mm 789

My VBA code is as following:

Dim cnnExcel As New ADODB.Connection
With cnnExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Test.xls"
End With

Dim rsExcel As New ADODB.Recordset
rsExcel.Open "Select * from [Sheet1$]", cnnExcel,
adOpenStatic

Do Until rsExcel.EOF
Debug.Print rsExcel.Fields(0).Value
Debug.Print rsExcel.Fields(1).Value
Debug.Print rsExcel.Fields(2).Value
Debug.Print rsExcel.Fields(3).Value
rsExcel.MoveNext
Loop

Do you know how I can solve this?

Unnur

.