Coldfusion Excel Datasource reading in blank string when a number is encountered
Posted on 2008-10-01
I have an excel 5.0/95 spreadsheet set up as a datasource for a Coldfusion script. It can read from the file using the following SQL command:
select password from [worksheet$] in 'C:\datasources\sheet.xls' 'EXCEL 5.0;';
And then I dump it using cfdump.
The problem is that when it encounters a number in the sheet, it does not read it in and displays '[empty string]'. I have tried formatting the column as both text and general and it does not seem to make any difference. I know that I can fix the problem by putting a single quote mark in front of the number in the spreadsheet, but this is not a practical solution as this is a regular task and will take a lot of time to go through and do this each time. I need to find a programatic solution.