Problem Reading Excel (.xls) file into VC++
-
Hello. I'm reading and excel (.xls) file using ODBC. For the most part it's working well, except I have a few columns that aren't being read accurately. The problem columns have data that can be either an int, text, or range such as int-int (ie. 12-16). The columns are specified as a general number format, although I have tried to change them to text. The problem is that the results from the query are not reading all the data formats. So in a columns described above, it may only read int values, but not the range value. When it queries a cell with the range data the result is empty. It seems that whatever type of value is first in the column defines which data type will be able to be read from the column. So if the first value in the column in a range (12-15), then only ranges will be accurately read, while int's will not. Is this a probem with the database being setup incorrectly? Or is there a way to read the data regardless? as a string say? Any thoughts on how I can fix this problem would be greatly appreciated. Thanks.
-
Hello. I'm reading and excel (.xls) file using ODBC. For the most part it's working well, except I have a few columns that aren't being read accurately. The problem columns have data that can be either an int, text, or range such as int-int (ie. 12-16). The columns are specified as a general number format, although I have tried to change them to text. The problem is that the results from the query are not reading all the data formats. So in a columns described above, it may only read int values, but not the range value. When it queries a cell with the range data the result is empty. It seems that whatever type of value is first in the column defines which data type will be able to be read from the column. So if the first value in the column in a range (12-15), then only ranges will be accurately read, while int's will not. Is this a probem with the database being setup incorrectly? Or is there a way to read the data regardless? as a string say? Any thoughts on how I can fix this problem would be greatly appreciated. Thanks.
The problem is in the logic used by the Excel ODBC driver. It treats the spreadsheet as if its rows and columns were rows and columns in a relational database table. As part of this, it assigns a datatype to each column. The problem is, a spreadsheet column may contain mixed datatypes. The ODBC driver makes a best guess at the datatype, based on the first couple of rows of data. Unfortunately, values for any cells that can't be expressed as this datatype instead appear as NULL. Check out MSDN Knowledge Base Q257819 ("How To Use ADO with Excel Data from Visual Basic or VB"), particularly the section entitled "A Caution about Mixed Data Types".