How to make coldfusion connect to an Excel file?

[mgiaconi]

help much appreciated := )

[sdupre]

Assume you're using 6.1 or 7.x ColdFusion.
If so, don't even screw around with ODBC. Lot's of minor issues but the major issue you have with ODBC is you won't get more than 255 characters from a column without some kung fu.

Go to www.hxtt.com and use their type 4 JDBC driver for Excel which is contained in a single .jar file. It's the only type 4 I know of.

Place that in /cfusionmx/runtime/servers/lib.

HXTT just went through an Excel driver beta that I participated in -due to an internal project -and we found a few issues and they were fixed.

Here's a typical URL to open up .xls files on the root of the C: drive using the HXTT driver.

URL: jdbc:excel:///c:/?FirstRowHasNames=true;versionNumber=BIFF8
Driver Class: com.hxtt.sql.excel.ExcelDriver

Each sheet (name) is a "table" name.

The HXTT driver handles unicode, etc without any problems.

They also have a very good Microsoft Access driver (type 4) that I've validated internally here with our MS Access test suite.

This doesn't mean "official" support per-se but it is a good driver and buys you JDBC stability for only ~$200, I think.

Stephen Dupre
Adobe Systems, Inc.

[mgiaconi]

I am creating a flash page which allows the user to select a XLS file, then to read that file with a cfm script. So, I started using the following way to read an excel file "microsoft jet"

<cfset tempfile = "c:\reportLdapAccount.xls">
<cfset sheetname = "reportLdapAccount">

<cfquery name="xls_src" datasource="proxy">
SELECT *
FROM "Excel 8.0; DATABASE=#tempfile#; HDR=YES".[#sheetName#$]
</cfquery>

this part works but I would like to
1) read the file the user wants to upload from flash
2) upload it via <cffile> in a cfm script
3) use the cfm script to read the xls file

my code only represents number 3)
number 2) doesn't work:
i(it says:CFFILE action="upload" requires forms to use enctype="multipart/form-data".)
BUT: I don't want to create the form in cfm but in Flash!
The code for 2) and 3) IS:

<cfset FileName ="c:\reportLdapAccount10.xls">
<cfset tempFile = GetTempFile(GetTempDirectory(), "xls")>
<cfset sheetname = "reportLdapAccount">

<cffile action = "upload"
fileField = "#FileName#"
destination = "#tempfile#"
nameConflict = "overwrite">

<cfquery name="excelData" datasource="proxy">
SELECT *
FROM "Excel 8.0; DATABASE=#tempfile#; HDR=YES".[#sheetName#$]
</cfquery>
<cfoutput> excelData.recordcount: #excelData.recordcount#</cfoutput>