Querying Excel Spreadsheets

A few weeks ago I had the need to query an Excel spreadsheet, several actually. There are a few different approaches you could take to get the Excel data into a ColdFusion recordset. You could import the spreadsheet into a database and query the database, or if you are handy with Java you could use Jakarta POI (see Matt Liotta's ExcelQuery CFX tag on OpenXCF for an example), but for what I was doing these options seemed like overkill. The approach I ended up taking used a dynamic datasource which allowed me to query my spreadsheets like so:

<cfquery name="test" datasource="dynamicXLS">
SELECT FirstName, LastName
FROM [Sheet1$]
IN 'C:\myXls.xls' 'EXCEL 8.0;'

You can find out how to do this in a TechNote on the Adobe site. One thing I will note, I'm using Excel 2003 (version 11.0), however the Microsoft Excel Driver used in the Windows XP ODBC Data Source Administrator only allows you to select up to Excel 97-2000. I found that this limited me to using 'EXCEL 8.0;' in my cfquery statements, but it seems to work fine.

UPDATE: I guess I missed this but Rob Gonda had a post on this same topic just a few months ago. He describes one additional method of using the JDBC drivers directly and provides a sample UDF based on this approach.

BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.