Read Excel File using ColdFusion and put in Query not working

Posted on 2011-10-21
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

    Accepted Solution

    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 51

    Expert Comment

       > 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 51

    Expert Comment

       > 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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now