Creating a Query from an Excel file
Today at work I had the need to retrieve the data from a sheet in a Microsoft Excel file. ColdFusion does not provide this functionality natively. Before reading on, please note that to use the below syntax, you must be allowed to create Java objects on your ColdFusion server. If this has been restricted because you are on a shared host, than this solution will not work for you.
I have created a ready-made function that will take care of the dirty work associated with getting a query from an Excel file. All you have to do is provide the relative file name of the Excel file you would like to read, and the name of a sheet within the Excel file that you would eventually like to query.
var c = "";
var stmnt = "";
var rs = "";
var sql = "Select * from [#sheetName#$]";
var myQuery = "";
arguments.filename = expandPath(arguments.filename);
if(len(trim(arguments.filename)) and fileExists(arguments.filename))
{
try
{
CreateObject("java","java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
c = CreateObject("java","java.sql.DriverManager").getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.filename );
stmnt = c.createStatement();
rs = stmnt.executeQuery(sql);
myQuery = CreateObject('java','coldfusion.sql.QueryTable').init(rs);
}
catch(any e)
{
// error-handling code }
}
}
return myQuery;
To execute the above code, you would simply call the function with the appropriate information as so:
myQuery = getExcelSheet("/mydocs/foobar.xls","customers");
As you can see I had to delve into create a few Java objects. The JDBC ODBC driver has support for reading Excel files which is what makes this all possible. The rest is fairly self-explainable. If you need further explanation, feel free to post a comment.
Can you please advise how I can query it without using a
sheetname, assuming there’s only 1 sheet in the file
The problem I’m having is with the sheetname, it doesnt seem
to take names with underscores and my file is generated
automatically and its important to have underscores in the
filename. However, excel just takes the filename and use that as
the sheetname
It was working yesterday but now everytime I try it gives me
this error
The value returned from function getExcelSheet() is not of type
query.
I havent changed anything!
I will have to look to see if you can access a page via an index or something like that. As for your getExcelSheet() is not of type query, do you want to post or send me an email of your code. You can catch me online for
Gtalk: mrkylehayes@gmail.com
AIM: mrkylehayes
yahoo: mrkylehayes@yahoo.com
msn: cfwise@hotmail.com
It works for some files and not others, i think it might depend on how the file is generated
In order to fix the problem with the sheet name, I use the following code, and I have that error
<cfoutput>
<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<!–[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>#basename#1#DateFormat(today,"ddmmyyyy")#</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]–>
<body>
<table>
<tr>
<cfloop list = "#qImages.columnList#" index="x">
<td>#x#</td>
</cfloop>
</tr>
<cfloop query="qImages">
<tr>
<cfloop list="#qImages.columnList#" index="i">
<td>#Evaluate(i)#</td>
</cfloop>
</tr>
</cfloop>
</table>
</body>
</head>
</html>
</cfoutput>
However, if i use the following code to generate, then most of the time it works fine
<cfoutput>
<cfset tabChar = chr(9)>
<cfset newLine = chr(13) & chr(10)>
<cfset sheet_headings=”>
<cfloop list = "#qImages.columnList#" index="x">
<cfset sheet_headings = listAppend(sheet_headings,x,’,')>
</cfloop>
#replace(sheet_headings,’,',tabchar,’all’)##newline#
<cfloop query="qImages">
<cfloop list="#qImages.columnList#" index="i">#evaluate(i)##iif(i eq listlast(qImages.columnList,’,'),de(newline),de(tabchar))#</cfloop>
</cfloop>
</cfoutput>
I just want to add one more thing that if I open the generated file in excel, then resave it as an actual excel file then it works, so its gotta be something to do with how excel sees the file
I see what you are saying. This code is not perfected and was only to be used from binary files that Excel generated. I never even thought about people generating their own from ColdFusion. That is very interesting.
I developed it because my customer needed to be able to upload an Excel spreadsheet to the server, that had a list of employee id numbers and add them to a database. Let me know if you have any suggestions.
I’ve used CFX_Excel2Query with a great deal of success. It’s fast and get’s the job done.
http://www.emerle.net/programming/display.cfm/t/cfx_excel2query
Ah yes, but it does cost. I needed a free solution. But thanks for the link.
You rock man! This thing saved my butt –
I attempting to get an OPENROWSET call working in a secured environment and learned the hard way – that accessing and getting file permissions to do such a task was no small feat w/o lowering security.
This will do it just fine!!
Thank you
Hey Kevin! I am glad I was able to help. I can only imagine everything that you must have gone through on the other route.
Hey Kyle – Thanks so much for posting this. I’d been trying to use a System DSN to access my excel file and that was causing some problems. This works great and I appreciate your posting it.
Hey CP, I am glad I could help. It was quite useful for me as well!
Hopefully someone is still reding this,as I would really like to use this function. I copied this exactly, but I get the error message:
Context validation error for tag cfscript.
The start tag must have a matching end tag. An explicit end tag can be provided by adding </cfscript>. If the body of the tag is empty you can use the shortcut <cfscript …/>.
The CFML compiler was processing:
a cfscript tag beginning on line 14, column 5.
a cfscript tag beginning on line 14, column 5.
___
ANy help would be appreciated. Thanks!
I would double / triple check that your parenthesis and brackets are matching each other/closed –
Looks like a syntax error in the cfscript.
Are you still having issues? I double-checked the code that I posted above and it all seems good.
I looked at the related ColdFusion Cookbook recipe (http://www.coldfusioncookbook.com/entry/114/How-do-I-return-a-query-from-an-Excel-file) that I had created as well based on this blog entry, it appears that other people were having the same issues as above. Ray, noticed there was missing bracket at the end some where and he fixed it.
I also fixed it in the code above, so give it a try now. Thanks.
But how do you find out how many sheets exist in an excel file?
That’s a good question.
Hey Kyle,
This script looks really neat, and I could really use it. I tested it out, and first I got the error saying my <cfscript> didn’t have an end tag. I added another bracket at the bottom and that fixed that. But now I’m getting an error saying that the return type is not of type query. Any ideas? I’ll copy the part of the code I changed below:
if(len(trim(arguments.filename)) and fileExists(arguments.filename)){
try{
CreateObject("java","java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
c = CreateObject("java","java.sql.DriverManager").getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.filename );
stmnt = c.createStatement();
rs = stmnt.executeQuery(sql);
myQuery = CreateObject(‘java’,'coldfusion.sql.QueryTable’).init(rs);
return myQuery;
}catch(any e){
// error-handling code }
}
}
I also tried placing the "return myQuery;" statement in different places around the brackets. That didn’t fix it. Your help would be greatly appreciated! Thanks!
Hey Victor, you may already have discovered this, but I just got the same error you described. I had the source file open in Excel, and it worked perfectly after I closed Excel and tried again.
Kyle, this is SO smooth. Thanks for making it available to us!
hey Victor,
I also got the error u were saying about , after making minor chnges it worked fine for me..
it is due to 2 reasons
1. Change the returntype="any" for function getExcelSheet
2 . The file name might not be taking the right path.
<!—Provide the full path of your excel file for the variable full_path—>
<cfset variables.full_path = "D:\test.xls">
<cfset myQuery = getExcelSheet(variables.full_path,"sheet1")>
<cffunction name="getExcelSheet" access="public" output="false" returntype="any">
<cfargument name="filename" required="true" type="string" />
<cfargument name="sheetName" required="true" type="string" />
<cfscript>
var c = "";
var stmnt = "";
var rs = "";
var sql = "Select * from [#sheetName#$]";
var myQuery = "";
if(len(trim(arguments.filename)) and fileExists(arguments.filename)){
try{
CreateObject("java","java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
c = CreateObject("java","java.sql.DriverManager").getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.filename );
stmnt = c.createStatement();
rs = stmnt.executeQuery(sql);
myQuery = CreateObject(‘java’,'coldfusion.sql.QueryTable’).init(rs);
}catch(any e){
//// error-handling code
}
}
return myQuery;
</cfscript>
</cffunction>
well as awesome as this is i’m running on mac os x. I was wondering if anyone out there has a solution for this. The function errors out on:
CreateObject("java","java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
it can’t resolve the sun.jdbc. any help would be great. Thanks.
Heh, unfortunately, when it comes to database drivers I am just as unknowing as you are. Perhaps anyone who is subscribed to this post will be able to comment.
Thank you Kyle for providing this solution. Wanted to let you know that if the spreadsheet you are working with has multiple data types, the code will attempt to select the most used type and return that.
In other words, I am working with a spreadsheet which has string and numerical data. When dumping, the numerical data has been cast as type string, and therefore shows up as [empty string]. Something to watch out for.
We did find another solution:
<cfquery name="qryGetData" datasource="#sDSN#">
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;HDR=NO;IMEX=1;Database=D:/Inetpub/wwwroot/nchsql/scripts/data/1764-20080113-USD-E.xls’,[Glance$])
</cfquery>
Code works great. I am also in need of code to pull a list of availble sheet names. How would I go about gathering the Metadata?
@Mark – This is a common question. Because of this, I have started to work on a solution. Stay posted, I hope to have a draft of a new ExcelFile CFC today.
Kyle – I got it to work. This will return the list as an Array, of Sheet names in the given Excel document
<cffunction name="getExcelSheet" access="public" output="false" returntype="any">
<cfargument name="filename" required="true" type="string" />
<cfscript>
var c = "";
var stmnt = "";
var rs = "";
var sheetList = "";
//if(len(trim(arguments.filename)) and fileExists(arguments.filename)){
try{
CreateObject("java","java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
c = CreateObject("java","java.sql.DriverManager").getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.filename & ";" );
stmnt = c.getMetaData();
rs = stmnt.getTables(javacast("null", ""),javacast("null", ""),javacast("null", ""),javacast("null", ""));
rsmd = rs.getMetaData();
sheetList = ArrayNew(1);
while(rs.next())
{
sheetList.addElement(rs.getString("TABLE_NAME"));
}
}catch(any e){
//// error-handling code
}
//}
return sheetList;
</cfscript>
</cffunction>
Indeed that is how you do it, but I am encapsulating it into a CFC with connection management etc so that it has a clean API. Thanks though!
Kyle,
Thank you for putting this up. It has saved me weeks of work and heartache. I really appreciate this.
Don
has anyone found a way to allow this code to read multiple excel spreadsheets? i need it to pull out values from two excel files, but can’t figure out a way to read more than one file at a time.
any help would be EXTREMELY appreciated.
Why couldn’t you just loop through the two files?
It all depends how you have your two files available to you, I will assume their file references are in an array:
<cfset queryArr = arrayNew(1) /> <!— Array to store returned queries —>
<cfloop from="1" to="#arrayLen(myFileArr)#" index="i">
<cfset arrayAppend(queryArr, getExcelSheet(myFileArr[i], ‘sheet1′)) />
</cfloop>
Obviously you have to customize that code based on how are you are storing your multiple file references, but that is the gist.
Hi,
This looks good but I’ve amateurish request, could I see how you actually impliment this? How do you actually query the inoformation and get it to display on your page? This code would be extremely helpful.
Thank You,
Jack
@Jack, you can just simply run a <cfdump var="#query#" /> to see the contents.
Otherwise, you can do <cftable query="#query#" /> to see a nicer display.
This works great for me except for one minor issue. Importing cells with integers return [empty string]. Any ideas?
Example:
COLA COLB
name ryan (import)
date 09/11/08 (import)
quantity 3 (empty)
Ideally you would add some code to tidy things up as you leave; particularly if there are errors with the Excel file or permissions issues, you need to close things up or reset the service to regain access to the .xls file. Inserting these lines just above the catch{} block seems to do the trick.
rs.close();
c.close();