Read Excel File using ColdFusion and put in Query not working

Posted on 2011-10-21
Medium Priority
Last Modified: 2012-05-12
What I  am trying to do is insert the data from the excel sheet and put into a database table.  I am working with the below code to  I tried with a cfcatch, but it just catches the error and returns "empty string"

 The excel sheet I am using has about 75 rows.

Below is the code that I am using:

<cffunction name="getExcelSheetData" access="public" output="false" returntype="query">
      <cfargument name="filename" required="true" type="string" />
      <cfargument name="sheetName" required="true" type="string" />
         var c = "";
         var stmnt = "";
         var rs = "";
         var sql = "Select * from [#arguments.sheetName#$]";
         var myQuery = "";
         arguments.filename = expandPath(arguments.filename);
         if(len(trim(arguments.filename)) and fileExists(arguments.filename)){
               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;
<cfset qry2 = getExcelSheetData("C:\inetpub\wwwroot\excelfilename.xls", "BillingSheet")>
<cfdump var="#qry2#">

Then I have the CF and SQL code to insert into database (this code is working properly). I am inserting into a MySQL database. If I use a the a built in CF function, getexcelSheet (<cfset myQuery = getExcelSheet("#UploadedFileName#","#left(qry.TABLE_NAME,len(qry.TABLE_NAME)-1)#")>),  I get results back, but with some unexpected glitches so I wanted to do use the above function so I can have more control over the query and query results.  When I do get results back, the SQL code to insert into the database table works just fine. The code that is not working is in the function above (getExcelSheetData).

So if I get a query back from the function with data in it, it will be smooth sailing from there.

The excel file name is correct and the path, so is the sheetname.

The error message that I receive is this:
The value returned from the getExcelSheetData function is not of type query.
If the component name is specified as a return type, its possible that a definition file for the component cannot be found or is not accessible.

The error comes back on the <cfset qry2 =  line of code.


Question by:cj_ervin
  • 2

Accepted Solution

cj_ervin earned 0 total points
ID: 37007808
I figured out the problem.

It was this line in the function:

arguments.filename = expandPath(arguments.filename);

I was passing the full path to the function, and then it was doubling it to look like this:

So it could not find the function and was not returning any data. Once I removed this line, it worked perfect.
LVL 52

Expert Comment

ID: 37007840
   > The value returned from the getExcelSheetData function is not of type query.

You can fix it so the function always returns a query - even when an error occurs. Just initialize myQuery as a query object instead of an empty string.

         var myQuery = queryNew("");

But that only fixes the symptom. The initial function was just an outline the author meant people to flesh out.  Especially the error handling part.  What do you want your app to do if:

- Bad file path / not found
- Bad sheet name
- If the file can't be read for some reason.

I don't think you should just swallow the error by catching it because the calling page wouldn't know something went wrong ...

LVL 52

Expert Comment

ID: 37007858
   > arguments.filename = expandPath(arguments.filename);

Yeah, I deliberately wrote my function to use an absolute path because relative paths make the code less flexible IMO.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question