Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type problem - ODBC Excel '97-2000 Driver
I'm writing a program in VC++ to interface with an excel
document and extract the elements I need. I'm using CRecordset and storing the cell value in a CDBVariant object. My problem is that I want to treat everything in the excel document as strings including any numeric data. Is there a way to do this? I guess the question boils down to whether one can have mixed data types in an excel column and have a recordset access that column and be able to read the different data types dynamically. Thanks to anyone with any suggestions... --ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type problem - ODBC Excel '97-2000 Driver
"Ryan" wrote ...
I'm writing a program in VC++ to interface with an excel document and extract the elements I need. I'm using CRecordset and storing the cell value in a CDBVariant object. My problem is that I want to treat everything in the excel document as strings including any numeric data. Is there a way to do this? I guess the question boils down to whether one can have mixed data types in an excel column and have a recordset access that column and be able to read the different data types dynamically. Here some notes on the subject: The relevant registry keys (for Jet 4.0) are in: Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ The ImportMixedTypes registry key is always read (whether it is honored is discussed later). You can test this by changing the key to ImportMixedTypes=OneDayWhen and trying to use the ISAM: you get the error, 'Invalid setting in Excel key of the Engines section of the Windows Registry.' The only valid values a ImportMixedTypes=Text ImportMixedTypes=Majority Type Data type is determined column by column. 'Majority Type' means a certain number of rows (more on this later) in each column are scanned and the data types are counted. Both a cell's value and format are used to determine data type. The majority data type (i.e. the one with the most rows) decides the overall data type for the entire column. There's a bias in favor os numeric in the event of a tie. Rows from any minority data types found that can't be cast as the majority data type will be returned with a null value. For ImportMixedTypes=Text, the data type for the whole column will be: Jet (MS Access UI): 'Text' data type DDL: VARCHAR(255) ADO: adWChar ('a null-terminated Unicode character string') Note that this is distinct from: Jet (MS Access UI): 'Memo' data type DDL: N/A ADO: adLongVarWChar ('a long null-terminated Unicode string value') ImportMixedTypes=Text will curtail text at 255 characters as 'Memo' is cast as 'Text'. For a column to be recognized as 'Memo', majority type must be detected, meaning the majority of rows detected must contain 256 or more characters. But how many rows are scanned for each column before is decided that mixed and/or what the majority type is? There is a second registry Key, TypeGuessRows. This can be a value from 0-16 (decimal). A value from 1 to 16 inclusive is the number of rows to scan. A value of zero means all rows will be scanned. There is one final twist. A setting of IMEX=1 in the connection string's extended property determines whether the ImportMixedTypes value is honored. IMEX refers to IMport EXport mode. There are three possible values. 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. The resulting connection string might look like this: Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\ db.xls; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1' Finally, although it is mentioned in MSDN articles that MAXSCANROWS can be used in the extended properties of the connection string to override the TypeGuessRows registry keys, this seems to be a fallacy. Using MAXSCANROWS=0 in this way never does anything under any circumstances. Put another way, is has just the same effect as putting ONEDAYWHEN=0 in the extended properties, being none (not even an error!). The same applied to ImportMixedTypes i.e. can't be used in the connection string to override the registry setting. In summary, use TypeGuessRows to get Jet to detect whether a 'mixed types' situation exists or use it to 'trick' Jet into detecting a certaint data type as being the majority type. In the event of a 'mixed types' situation being detected, use ImportMixedTypes to tell Jet to either use the majority type or coerce all values as 'Text' (max 255 characters). -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type problem - ODBC Excel '97-2000 Driver
Thank you for the information!
--ryan -----Original Message----- "Ryan" wrote ... I'm writing a program in VC++ to interface with an excel document and extract the elements I need. I'm using CRecordset and storing the cell value in a CDBVariant object. My problem is that I want to treat everything in the excel document as strings including any numeric data. Is there a way to do this? I guess the question boils down to whether one can have mixed data types in an excel column and have a recordset access that column and be able to read the different data types dynamically. Here some notes on the subject: The relevant registry keys (for Jet 4.0) are in: Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Exce l/ The ImportMixedTypes registry key is always read (whether it is honored is discussed later). You can test this by changing the key to ImportMixedTypes=OneDayWhen and trying to use the ISAM: you get the error, 'Invalid setting in Excel key of the Engines section of the Windows Registry.' The only valid values a ImportMixedTypes=Text ImportMixedTypes=Majority Type Data type is determined column by column. 'Majority Type' means a certain number of rows (more on this later) in each column are scanned and the data types are counted. Both a cell's value and format are used to determine data type. The majority data type (i.e. the one with the most rows) decides the overall data type for the entire column. There's a bias in favor os numeric in the event of a tie. Rows from any minority data types found that can't be cast as the majority data type will be returned with a null value. For ImportMixedTypes=Text, the data type for the whole column will be: Jet (MS Access UI): 'Text' data type DDL: VARCHAR(255) ADO: adWChar ('a null-terminated Unicode character string') Note that this is distinct from: Jet (MS Access UI): 'Memo' data type DDL: N/A ADO: adLongVarWChar ('a long null-terminated Unicode string value') ImportMixedTypes=Text will curtail text at 255 characters as 'Memo' is cast as 'Text'. For a column to be recognized as 'Memo', majority type must be detected, meaning the majority of rows detected must contain 256 or more characters. But how many rows are scanned for each column before is decided that mixed and/or what the majority type is? There is a second registry Key, TypeGuessRows. This can be a value from 0-16 (decimal). A value from 1 to 16 inclusive is the number of rows to scan. A value of zero means all rows will be scanned. There is one final twist. A setting of IMEX=1 in the connection string's extended property determines whether the ImportMixedTypes value is honored. IMEX refers to IMport EXport mode. There are three possible values. 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. The resulting connection string might look like this: Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\ db.xls; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1' Finally, although it is mentioned in MSDN articles that MAXSCANROWS can be used in the extended properties of the connection string to override the TypeGuessRows registry keys, this seems to be a fallacy. Using MAXSCANROWS=0 in this way never does anything under any circumstances. Put another way, is has just the same effect as putting ONEDAYWHEN=0 in the extended properties, being none (not even an error!). The same applied to ImportMixedTypes i.e. can't be used in the connection string to override the registry setting. In summary, use TypeGuessRows to get Jet to detect whether a 'mixed types' situation exists or use it to 'trick' Jet into detecting a certaint data type as being the majority type. In the event of a 'mixed types' situation being detected, use ImportMixedTypes to tell Jet to either use the majority type or coerce all values as 'Text' (max 255 characters). -- . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel ODBC driver created table/data not persistent | Excel Discussion (Misc queries) | |||
ODBC Driver Manager: Option type out of range | Excel Discussion (Misc queries) | |||
Free XML ODBC driver for EXCEL | Excel Discussion (Misc queries) | |||
ODBC Driver/Import Data | Excel Discussion (Misc queries) | |||
xl odbc driver, Excel 2003 | Setting up and Configuration of Excel |