Inside Out Outside In

ColdFusion Q&D;: Reading Excel Data

We often get so wrapped up in frameworks, reusable code and OO that we forget about using ColdFusion for those times when you need a one off or a Quick And Dirty utility.   Ben Nadel has a great post on using JExcelObject.   The post goes over in detail how to use the code to read data from an Excel spreadsheet into a query, make that a lot of code.

There is a simpler approach you can take that uses quite a bit less code.  Just define a System DSN ODBC datasource pointing to the Excel spreadsheet and then use CFQUERY against the datasource.

In this example I used the USDA National Nutrient Database.  One slight warning.  I don't recommend a "SELECT *".  Excel isn't optimized for SQL and depending on how much power/ram your system has, you may be able to go drink a pot of coffee, brew another pot, read the paper, etc.

Here's a KB article from Microsoft that references how to access Excel from VB, which should give you a base to work with regarding select statements. 

You have to reference the worksheet name or named ranges using either a `worksheetName$` or [worksheetName$] syntax.  If the worksheet has spaces, you'll have to preserve the spaces in the query.

ODBC Driver for Excel

Driver do Microsoft Excel

 

DSN Setup


CF Datasource Setup

Using slanted quote in query

 

<cfquery name="qryExcel" datasource="sr17" maxrows="200">
select SHRT_DESC, VIT_C
from `ABBREV$` where VIT_C > 0
</cfquery>

 

Result

Using bracket notation and a range

 

<cfquery name="qryExcel" datasource="sr17" maxrows="24">
SELECT * FROM [ABBREV$B1:E24]
</cfquery>

 

Result


Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Ben Nadel's Gravatar Chris,\n\nI have had other people as this place use that methodology with great success. A most excellent option.
# Posted By Ben Nadel | 12/5/06 2:45 PM
tony petruzzi's Gravatar touche Chris, touche. I keep forgetting that you can do this with excel.
# Posted By tony petruzzi | 12/5/06 4:42 PM
Scot's Gravatar Ben's way will work in a shared hosting environment. Without the need to setup a dsn.
# Posted By Scot | 12/6/06 10:27 AM
Christopher Wigginton's Gravatar Scot,\n\nYou're right, but then that really wouldn't be a one off or a Q&D. For a long term method, you would probably want to go with something along Ben's method, which would be great to automate a process of the uploading of Excel documents and then processing the Excel document into a database.\n\nThat is assuming that in the shared hosting environment they haven't disallowed createObject(Java) through the sandbox, which is a big issue if you're on the cheap. Take my case for instance, I have 5 hosted domains for a grand total of $17 a month (just under $3.50 a domain) with ColdFusion, MSSQL, MySQL and other standard tech. What I don't have is createObject(Java) but I can create DSN's (not through the CFIDE, through their admin interface) \n\nNow if any hosting partners can give me ColdFusion MX 7, createObject(Java); and complete isolation and security at $3.50 a month per domain, oh, and throw in CFIDE access as well, I might consider jumping :-)
# Posted By Christopher Wigginton | 12/6/06 12:43 PM
Craig Spanburg's Gravatar This worked for a long time but now when I try to create a new Data Source using an ODBC Socket I get the following error message in the ColdFusion Administrator.\n\n"Unable to update the ColdFusion MX 7 ODBC Server.\nTimeout period expired without completion of C:\\CFusionMX7\\db\\slserver54\\admin\\swcla.exe"\n\nOddly, the existing data sources using an odbc socket still work. Both ODBC services are running.\nAny ideas?
# Posted By Craig Spanburg | 5/8/07 9:46 AM
Christopher Wigginton's Gravatar Craig,\n\nNot sure why it's not working for you. Is it a large spreadsheet? Can you create a datasource for a new small spreadsheet?
# Posted By Christopher Wigginton | 5/17/07 3:42 PM
Greg's Gravatar The images are all broken. I assume these were screenshots of the spreadsheet?
# Posted By Greg | 3/20/08 4:12 PM
Christopher Wigginton's Gravatar Sorry about the image links. My previous hosting provider went belly up and I didn't have a backup of the images. Fortunately I was able to recover the text.
# Posted By Christopher Wigginton | 3/22/08 9:57 PM