Link to home
Start Free TrialLog in
Avatar of cj_ervin
cj_ervin

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of cj_ervin
cj_ervin

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of _agx_
   > 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 ...

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