Read Excel File using ColdFusion and put in Query not working

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" />
      <cfscript>
         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)){
            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>            
   
<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.

Thanks

cj_ervinAsked:
Who is Participating?
 
cj_ervinConnect With a Mentor Author Commented:
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:
C:\inetpub\wwwroot\excelfilename.xlsC:\inetpub\wwwroot\excelfilename.xls

So it could not find the function and was not returning any data. Once I removed this line, it worked perfect.
0
 
_agx_Commented:
   > 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 ...

0
 
_agx_Commented:
   > 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.