> I am running a query on an Excel spreadsheet using the MS Excel
> ODBC driver.
> I have the columns named in the top row of the spreadsheet. My problem
> occurs in one of the columns where there is a text header on row
> 5 then rows
> 7-11 have numeric data. When I run the query it is only bringing
> back the
> text, not the numbers. When I change the numbers to text they
> are selected.
> Does anyone know what might cause this?
OK, I know it's been a while since this was asked, but since I only just
wading through my CF-Talk mails, I just found this not answered
Here's how the Excel ODBC (and for that case, Excel via Access ODBC)
What Excel does is to decide what field type the column is - it looks at the
top rows (depending on how big the file is and how many columns - M$
couldn't give me an exact number) and then fixes the column at that type
After that decision, all entries in that column that are not of the same
type (e.g. a Numeric in a Text or Text in a Numeric) are ignored - yup, you
read that right, ignored!
It's incredibily annoying, but that's the way Excel ODBC works
As a get-around (this requires some work before the file is loaded to the
server), convert all fields to Text - and I don't mean Format/Cells/Text -
you can do this by making a new sheet (and naming it if necessary) and then
running the following function to convert it to a string;
=MID("'"&SheetName!A1,2,Len(SheetName!A1))
Obviously the SheetName and Cell are dynamic, but you should get the gist
HTH (even if it's a little late)
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
