Leading Zeros not being read from Excel Query

[cmkelly]

We are having an odd problem when trying to read in a query from Excel.

What we are trying to do: We have a page where users will upload an Excel file
through a form, we make a copy of the file on the server and then try to read
in that data to write to our database.

Our Problem: Our CFQUERY reads almost everything correctly. The only problem
is that one of the fields in the Excel file contains a nine digit number (SSN).
This nine digit number will sometimes contain leading zeros (ex. 012345678). In
excel, if the cell is a number, it tries to strip the leading zeros. If you
change the cell to a text field, it displays in excel exactly the way it was
typed in. However, a CFDUMP of our query reveals that those numbers that start
with a zero, comes in as "empty string".

What we have tried: We have tried putting in alphanumeric strings that start
with zero (ex. 0ABCD) with the same result. We tried changing the formatting in
excel to a special/custom type which only changed the display in Excel. We
tried saving the file out as various versions (95, 97, etc.).

Has anybody else had this problem or have any ideas??

[mxstu]

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
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;257819[/url]


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.

Good luck

[belie]

This is an old topic but here's how I was able to solve it (my problem was with
zip codes starting with a 0 and some of the data being 10 digit zip codes):
select * from `sheet_name_here$` IN 'C:\SomeDirectory\myfile.xls' 'EXCEL
5.0;IMEX=1'

the 'EXCEL 5.0;IMEX=1' forced everything to be a string i believe.