How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel file?

Hi Mike,

You can use a combination of ADO and ADOX to do this:

Sub test()
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog

cnn.Open "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=c:\test.xls"
Set cat.ActiveConnection = cnn
For Each tbl In cat.Tables
Debug.Print "Table: " & Left$(tbl.Name, Len(tbl.Name) - 1)
For Each col In tbl.Columns
Debug.Print " Column: " & col.Name
Debug.Print " Type: " & col.Type
Next col
Next tbl

Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Sub

The Type property will give you a long value - you'll have to match those up
with the DataTypeEnum enumeration, which is part of the ADOX library.

For this code to work, you must set a reference to "Microsoft ActiveX Data
Objects 2.x Library" and "Microsoft ADO Ext. 2.x for DDL and Security".

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


mike wrote:
How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel file?

Jake,
The OP specified OLEDB Provider for Jet. Your example uses the
Microsoft ODBC Provider, which is considered a deprecated component in
favor of the OLEDB Provider for Jet anyhow. See:

http://msdn.microsoft.com/library/de...deprecated.asp

I have some code from a previous project which provides descriptions
for all values of DataTypeEnum (overkill for Excel, I know!), so I use
only late bound ADODB (i.e. no ADOX). Therefore, I offer the following
alternative code:

Option Explicit

Sub GetSchema()

Dim oConn As Object
Dim oRs As Object

Set oConn = CreateObject("ADODB.Connection")

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test.xls;" & _
"Extended Properties='Excel 8.0'"

Set oRs = oConn.OpenSchema(adSchemaColumns)

With oRs

Do While Not .EOF

Debug.Print "Sheet: " & !TABLE_NAME
Debug.Print "Column: " & !COLUMN_NAME
Debug.Print "Data type: " & GetDataType(!DATA_TYPE, True)

.MoveNext

Loop

End With

oConn.Close

End Sub

Private Function GetDataType(ByVal DataType As Long, _
Optional ByVal Description As Boolean = False) As String

If Description Then

Select Case DataType
Case 0: GetDataType = "no value"
Case 2: GetDataType = "a two-byte signed integer "
Case 3: GetDataType = "a four-byte signed integer "
Case 4: GetDataType = "a single-precision floating-point value "
Case 5: GetDataType = "a double-precision floating-point value "
Case 6: GetDataType = "a currency value "
Case 7: GetDataType = "a date value "
Case 8: GetDataType = "a null-terminated character string "
Case 9: GetDataType = "a pointer to an IDispatch interface on a
COM object "
Case 10: GetDataType = "a 32-bit error code "
Case 11: GetDataType = "a boolean value "
Case 12: GetDataType = "an Automation Variant "
Case 13: GetDataType = "a pointer to an IUnknown interface on a
COM object "
Case 14: GetDataType = "an exact numeric value with a fixed
precision and scale "
Case 16: GetDataType = "a one-byte signed integer "
Case 17: GetDataType = "a one-byte unsigned integer "
Case 18: GetDataType = "a two-byte unsigned integer "
Case 19: GetDataType = "a four-byte unsigned integer "
Case 20: GetDataType = "an eight-byte signed integer "
Case 21: GetDataType = "an eight-byte unsigned integer "
Case 64: GetDataType = "a 64-bit value representing the number of
100-nanosecond intervals since January 1, 1601 "
Case 72: GetDataType = "a globally unique identifier "
Case 128: GetDataType = "a binary value "
Case 129: GetDataType = "a string value "
Case 130: GetDataType = "a null-terminated Unicode character
string "
Case 131: GetDataType = "an exact numeric value with a fixed
precision and scale "
Case 132: GetDataType = "a user-defined variable "
Case 133: GetDataType = "a date value "
Case 134: GetDataType = "a time value "
Case 135: GetDataType = "a date/time stamp "
Case 136: GetDataType = "a four-byte chapter value that identifies
rows in a child rowset "
Case 138: GetDataType = "an Automation PROPVARIANT "
Case 139: GetDataType = "a numeric value "
Case 200: GetDataType = "a string value"
Case 201: GetDataType = "a long string value"
Case 202: GetDataType = "a null-terminated Unicode character
string"
Case 203: GetDataType = "a long null-terminated Unicode string
value."
Case 204: GetDataType = "a binary value "
Case 205: GetDataType = "a long binary value"
End Select

Else

Select Case DataType
Case 0: GetDataType = "adEmpty"
Case 2: GetDataType = "adSmallInt"
Case 3: GetDataType = "adInteger"
Case 4: GetDataType = "adSingle"
Case 5: GetDataType = "adDouble"
Case 6: GetDataType = "adCurrency"
Case 7: GetDataType = "adDate"
Case 8: GetDataType = "adBSTR"
Case 9: GetDataType = "adIDispatch"
Case 10: GetDataType = "adError"
Case 11: GetDataType = "adBoolean"
Case 12: GetDataType = "adVariant"
Case 13: GetDataType = "adIUnknown"
Case 14: GetDataType = "adDecimal"
Case 16: GetDataType = "adTinyInt"
Case 17: GetDataType = "adUnsignedTinyInt"
Case 18: GetDataType = "adUnsignedSmallInt"
Case 19: GetDataType = "adUnsignedInt"
Case 20: GetDataType = "adBigInt"
Case 21: GetDataType = "adUnsignedBigInt"
Case 64: GetDataType = "adFileTime"
Case 72: GetDataType = "adGUID"
Case 128: GetDataType = "adBinary"
Case 129: GetDataType = "adChar"
Case 130: GetDataType = "adWChar"
Case 131: GetDataType = "adNumeric"
Case 132: GetDataType = "adUserDefined"
Case 133: GetDataType = "adDBDate"
Case 134: GetDataType = "adDBTime"
Case 135: GetDataType = "adDBTimeStamp"
Case 136: GetDataType = "adChapter"
Case 138: GetDataType = "adPropVariant"
Case 139: GetDataType = "adVarNumeric"
Case 200: GetDataType = "adVarChar"
Case 201: GetDataType = "adLongVarChar"
Case 202: GetDataType = "adVarWChar"
Case 203: GetDataType = "adLongVarWChar"
Case 204: GetDataType = "adVarBinary"
Case 205: GetDataType = "adLongVarBinary"

End Select

End If

End Function

--

"Jake Marx" wrote in message ...
Hi Mike,

You can use a combination of ADO and ADOX to do this:

Sub test()
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog

cnn.Open "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=c:\test.xls"
Set cat.ActiveConnection = cnn
For Each tbl In cat.Tables
Debug.Print "Table: " & Left$(tbl.Name, Len(tbl.Name) - 1)
For Each col In tbl.Columns
Debug.Print " Column: " & col.Name
Debug.Print " Type: " & col.Type
Next col
Next tbl

Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Sub

The Type property will give you a long value - you'll have to match those up
with the DataTypeEnum enumeration, which is part of the ADOX library.

For this code to work, you must set a reference to "Microsoft ActiveX Data
Objects 2.x Library" and "Microsoft ADO Ext. 2.x for DDL and Security".

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


mike wrote:
How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel file?

Jake,
The OP specified OLEDB Provider for Jet. Your example uses the
Microsoft ODBC Provider, which is considered a deprecated component in
favor of the OLEDB Provider for Jet anyhow.

True - I missed that. I was posting some older code. Thanks for the
correction.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

First, it works fine thanks! But I'm getting some strange
results/behaivor, for example:

Col1 is formatted as date with values: NA, NA, 1/1/2000,
1/1/2002
Returns datatype adDate, good that's what I want.

Col2 also formatted as date with
values:12/30/2003,2/5/2002,NA,NA
Returns datatype adWChar, not good, WHY?

This also occurs on random number and currency columns, I
really don't understand this. There does not seem to be
any logic to this, those are real examples and these weird
results don't have any pattern to them even when 2 cols
have similar values, I'm stumpted.
Do you have any ideas?
The same happens when i use the DTS Wizard to import into
SQL Server!!!
Thanks




-----Original Message-----
Jake,
The OP specified OLEDB Provider for Jet. Your example
uses the
Microsoft ODBC Provider, which is considered a deprecated
component in
favor of the OLEDB Provider for Jet anyhow. See:

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/oledb/htm/oledb_deprecated.asp

I have some code from a previous project which provides
descriptions
for all values of DataTypeEnum (overkill for Excel, I
know!), so I use
only late bound ADODB (i.e. no ADOX). Therefore, I offer
the following
alternative code:

Option Explicit

Sub GetSchema()

Dim oConn As Object
Dim oRs As Object

Set oConn = CreateObject("ADODB.Connection")

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test.xls;" & _
"Extended Properties='Excel 8.0'"

Set oRs = oConn.OpenSchema(adSchemaColumns)

With oRs

Do While Not .EOF

Debug.Print "Sheet: " & !TABLE_NAME
Debug.Print "Column: " & !COLUMN_NAME
Debug.Print "Data type: " & GetDataType(!DATA_TYPE,
True)

.MoveNext

Loop

End With

oConn.Close

End Sub

Private Function GetDataType(ByVal DataType As Long, _
Optional ByVal Description As Boolean = False) As String

If Description Then

Select Case DataType
Case 0: GetDataType = "no value"
Case 2: GetDataType = "a two-byte signed integer "
Case 3: GetDataType = "a four-byte signed integer "
Case 4: GetDataType = "a single-precision floating-
point value "
Case 5: GetDataType = "a double-precision floating-
point value "
Case 6: GetDataType = "a currency value "
Case 7: GetDataType = "a date value "
Case 8: GetDataType = "a null-terminated character
string "
Case 9: GetDataType = "a pointer to an IDispatch
interface on a
COM object "
Case 10: GetDataType = "a 32-bit error code "
Case 11: GetDataType = "a boolean value "
Case 12: GetDataType = "an Automation Variant "
Case 13: GetDataType = "a pointer to an IUnknown
interface on a
COM object "
Case 14: GetDataType = "an exact numeric value with a
fixed
precision and scale "
Case 16: GetDataType = "a one-byte signed integer "
Case 17: GetDataType = "a one-byte unsigned integer "
Case 18: GetDataType = "a two-byte unsigned integer "
Case 19: GetDataType = "a four-byte unsigned integer "
Case 20: GetDataType = "an eight-byte signed integer "
Case 21: GetDataType = "an eight-byte unsigned
integer "
Case 64: GetDataType = "a 64-bit value representing
the number of
100-nanosecond intervals since January 1, 1601 "
Case 72: GetDataType = "a globally unique identifier "
Case 128: GetDataType = "a binary value "
Case 129: GetDataType = "a string value "
Case 130: GetDataType = "a null-terminated Unicode
character
string "
Case 131: GetDataType = "an exact numeric value with
a fixed
precision and scale "
Case 132: GetDataType = "a user-defined variable "
Case 133: GetDataType = "a date value "
Case 134: GetDataType = "a time value "
Case 135: GetDataType = "a date/time stamp "
Case 136: GetDataType = "a four-byte chapter value
that identifies
rows in a child rowset "
Case 138: GetDataType = "an Automation PROPVARIANT "
Case 139: GetDataType = "a numeric value "
Case 200: GetDataType = "a string value"
Case 201: GetDataType = "a long string value"
Case 202: GetDataType = "a null-terminated Unicode
character
string"
Case 203: GetDataType = "a long null-terminated
Unicode string
value."
Case 204: GetDataType = "a binary value "
Case 205: GetDataType = "a long binary value"
End Select

Else

Select Case DataType
Case 0: GetDataType = "adEmpty"
Case 2: GetDataType = "adSmallInt"
Case 3: GetDataType = "adInteger"
Case 4: GetDataType = "adSingle"
Case 5: GetDataType = "adDouble"
Case 6: GetDataType = "adCurrency"
Case 7: GetDataType = "adDate"
Case 8: GetDataType = "adBSTR"
Case 9: GetDataType = "adIDispatch"
Case 10: GetDataType = "adError"
Case 11: GetDataType = "adBoolean"
Case 12: GetDataType = "adVariant"
Case 13: GetDataType = "adIUnknown"
Case 14: GetDataType = "adDecimal"
Case 16: GetDataType = "adTinyInt"
Case 17: GetDataType = "adUnsignedTinyInt"
Case 18: GetDataType = "adUnsignedSmallInt"
Case 19: GetDataType = "adUnsignedInt"
Case 20: GetDataType = "adBigInt"
Case 21: GetDataType = "adUnsignedBigInt"
Case 64: GetDataType = "adFileTime"
Case 72: GetDataType = "adGUID"
Case 128: GetDataType = "adBinary"
Case 129: GetDataType = "adChar"
Case 130: GetDataType = "adWChar"
Case 131: GetDataType = "adNumeric"
Case 132: GetDataType = "adUserDefined"
Case 133: GetDataType = "adDBDate"
Case 134: GetDataType = "adDBTime"
Case 135: GetDataType = "adDBTimeStamp"
Case 136: GetDataType = "adChapter"
Case 138: GetDataType = "adPropVariant"
Case 139: GetDataType = "adVarNumeric"
Case 200: GetDataType = "adVarChar"
Case 201: GetDataType = "adLongVarChar"
Case 202: GetDataType = "adVarWChar"
Case 203: GetDataType = "adLongVarWChar"
Case 204: GetDataType = "adVarBinary"
Case 205: GetDataType = "adLongVarBinary"

End Select

End If

End Function

--

"Jake Marx" wrote in message
...
Hi Mike,

You can use a combination of ADO and ADOX to do this:

Sub test()
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog

cnn.Open "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=c:\test.xls"
Set cat.ActiveConnection = cnn
For Each tbl In cat.Tables
Debug.Print "Table: " & Left$(tbl.Name, Len
(tbl.Name) - 1)
For Each col In tbl.Columns
Debug.Print " Column: " & col.Name
Debug.Print " Type: " & col.Type
Next col
Next tbl

Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Sub

The Type property will give you a long value - you'll
have to match those up
with the DataTypeEnum enumeration, which is part of the
ADOX library.

For this code to work, you must set a reference
to "Microsoft ActiveX Data
Objects 2.x Library" and "Microsoft ADO Ext. 2.x for
DDL and Security".

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


mike wrote:
How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel
file?
.

Standard answer number 3:

Check the following registry settings for the *machine*:

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

TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
all column values before choosing the appropriate data type.

ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text:

Using IMEX=1 in the connection string ensures the registry setting is
applied.

Specific answer: I guess the NA is causing the datatype to be
determined as text. Is it possible to replace NA with
null/blank/zero/an actual date that signifies a null in your business
logic?

--

"Mike" wrote in message ...
First, it works fine thanks! But I'm getting some strange
results/behaivor, for example:

Col1 is formatted as date with values: NA, NA, 1/1/2000,
1/1/2002
Returns datatype adDate, good that's what I want.

Col2 also formatted as date with
values:12/30/2003,2/5/2002,NA,NA
Returns datatype adWChar, not good, WHY?

This also occurs on random number and currency columns, I
really don't understand this. There does not seem to be
any logic to this, those are real examples and these weird
results don't have any pattern to them even when 2 cols
have similar values, I'm stumpted.
Do you have any ideas?
The same happens when i use the DTS Wizard to import into
SQL Server!!!
Thanks




-----Original Message-----
Jake,
The OP specified OLEDB Provider for Jet. Your example
uses the
Microsoft ODBC Provider, which is considered a deprecated
component in
favor of the OLEDB Provider for Jet anyhow. See:

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/oledb/htm/oledb_deprecated.asp

I have some code from a previous project which provides
descriptions
for all values of DataTypeEnum (overkill for Excel, I
know!), so I use
only late bound ADODB (i.e. no ADOX). Therefore, I offer
the following
alternative code:

Option Explicit

Sub GetSchema()

Dim oConn As Object
Dim oRs As Object

Set oConn = CreateObject("ADODB.Connection")

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test.xls;" & _
"Extended Properties='Excel 8.0'"

Set oRs = oConn.OpenSchema(adSchemaColumns)

With oRs

Do While Not .EOF

Debug.Print "Sheet: " & !TABLE_NAME
Debug.Print "Column: " & !COLUMN_NAME
Debug.Print "Data type: " & GetDataType(!DATA_TYPE,
True)

.MoveNext

Loop

End With

oConn.Close

End Sub

Private Function GetDataType(ByVal DataType As Long, _
Optional ByVal Description As Boolean = False) As String

If Description Then

Select Case DataType
Case 0: GetDataType = "no value"
Case 2: GetDataType = "a two-byte signed integer "
Case 3: GetDataType = "a four-byte signed integer "
Case 4: GetDataType = "a single-precision floating-
point value "
Case 5: GetDataType = "a double-precision floating-
point value "
Case 6: GetDataType = "a currency value "
Case 7: GetDataType = "a date value "
Case 8: GetDataType = "a null-terminated character
string "
Case 9: GetDataType = "a pointer to an IDispatch
interface on a
COM object "
Case 10: GetDataType = "a 32-bit error code "
Case 11: GetDataType = "a boolean value "
Case 12: GetDataType = "an Automation Variant "
Case 13: GetDataType = "a pointer to an IUnknown
interface on a
COM object "
Case 14: GetDataType = "an exact numeric value with a
fixed
precision and scale "
Case 16: GetDataType = "a one-byte signed integer "
Case 17: GetDataType = "a one-byte unsigned integer "
Case 18: GetDataType = "a two-byte unsigned integer "
Case 19: GetDataType = "a four-byte unsigned integer "
Case 20: GetDataType = "an eight-byte signed integer "
Case 21: GetDataType = "an eight-byte unsigned
integer "
Case 64: GetDataType = "a 64-bit value representing
the number of
100-nanosecond intervals since January 1, 1601 "
Case 72: GetDataType = "a globally unique identifier "
Case 128: GetDataType = "a binary value "
Case 129: GetDataType = "a string value "
Case 130: GetDataType = "a null-terminated Unicode
character
string "
Case 131: GetDataType = "an exact numeric value with
a fixed
precision and scale "
Case 132: GetDataType = "a user-defined variable "
Case 133: GetDataType = "a date value "
Case 134: GetDataType = "a time value "
Case 135: GetDataType = "a date/time stamp "
Case 136: GetDataType = "a four-byte chapter value
that identifies
rows in a child rowset "
Case 138: GetDataType = "an Automation PROPVARIANT "
Case 139: GetDataType = "a numeric value "
Case 200: GetDataType = "a string value"
Case 201: GetDataType = "a long string value"
Case 202: GetDataType = "a null-terminated Unicode
character
string"
Case 203: GetDataType = "a long null-terminated
Unicode string
value."
Case 204: GetDataType = "a binary value "
Case 205: GetDataType = "a long binary value"
End Select

Else

Select Case DataType
Case 0: GetDataType = "adEmpty"
Case 2: GetDataType = "adSmallInt"
Case 3: GetDataType = "adInteger"
Case 4: GetDataType = "adSingle"
Case 5: GetDataType = "adDouble"
Case 6: GetDataType = "adCurrency"
Case 7: GetDataType = "adDate"
Case 8: GetDataType = "adBSTR"
Case 9: GetDataType = "adIDispatch"
Case 10: GetDataType = "adError"
Case 11: GetDataType = "adBoolean"
Case 12: GetDataType = "adVariant"
Case 13: GetDataType = "adIUnknown"
Case 14: GetDataType = "adDecimal"
Case 16: GetDataType = "adTinyInt"
Case 17: GetDataType = "adUnsignedTinyInt"
Case 18: GetDataType = "adUnsignedSmallInt"
Case 19: GetDataType = "adUnsignedInt"
Case 20: GetDataType = "adBigInt"
Case 21: GetDataType = "adUnsignedBigInt"
Case 64: GetDataType = "adFileTime"
Case 72: GetDataType = "adGUID"
Case 128: GetDataType = "adBinary"
Case 129: GetDataType = "adChar"
Case 130: GetDataType = "adWChar"
Case 131: GetDataType = "adNumeric"
Case 132: GetDataType = "adUserDefined"
Case 133: GetDataType = "adDBDate"
Case 134: GetDataType = "adDBTime"
Case 135: GetDataType = "adDBTimeStamp"
Case 136: GetDataType = "adChapter"
Case 138: GetDataType = "adPropVariant"
Case 139: GetDataType = "adVarNumeric"
Case 200: GetDataType = "adVarChar"
Case 201: GetDataType = "adLongVarChar"
Case 202: GetDataType = "adVarWChar"
Case 203: GetDataType = "adLongVarWChar"
Case 204: GetDataType = "adVarBinary"
Case 205: GetDataType = "adLongVarBinary"

End Select

End If

End Function

--

"Jake Marx" wrote in message
...
Hi Mike,

You can use a combination of ADO and ADOX to do this:

Sub test()
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog

cnn.Open "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=c:\test.xls"
Set cat.ActiveConnection = cnn
For Each tbl In cat.Tables
Debug.Print "Table: " & Left$(tbl.Name, Len
(tbl.Name) - 1)
For Each col In tbl.Columns
Debug.Print " Column: " & col.Name
Debug.Print " Type: " & col.Type
Next col
Next tbl

Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Sub

The Type property will give you a long value - you'll
have to match those up
with the DataTypeEnum enumeration, which is part of the
ADOX library.

For this code to work, you must set a reference
to "Microsoft ActiveX Data
Objects 2.x Library" and "Microsoft ADO Ext. 2.x for
DDL and Security".

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


mike wrote:
How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel
file?
.