M
mike
How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel file?
number, names, and datatypes of columns in an excel file?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jake Marx
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]
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]
O
onedaywhen
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
--
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 said: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]
How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel file?
J
Jake Marx
Jake,
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]
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]
M
Mike
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
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" <[email protected]> wrote in messagefile?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]
How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel
.
O
onedaywhen
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?
--
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 said: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" <[email protected]> wrote in messagefile?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
.