That is an old issue with querying excel files that goes way back. If you
search the old forum archives for EXCEL there are many posts on this topic. If
you cannot access the archives through this site, you might want to try
searching through google groups instead.
The basic problem is caused by how the driver determines the column data
types. When you query a database table, each column has a single, known, data
type. When you query an Excel file, the spreadsheet columns do not have data
types. The driver has to guess each column's type by using the values in that
column. Sometimes it guesses correctly and other times it doesn't.
These links do not refer to cfquery, but the do describe the problem
There were a number of suggested work arounds, such as:
- Setting the column in excel to type text and re-pasting the values
- Setting the "rows to scan" value
- Using a setting called IMEX
- Saving the work sheet as a text file and using a "schema.ini" file when
reading in the file
I don't remember the specifics about which method... if any of them ...
actually worked, but this may at least give you somewhere to start looking.