Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Funky (Unexpected) Results Returned when Reading exel file with SQL

Posted on 2011-10-25
17
Medium Priority
?
896 Views
Last Modified: 2012-05-12
I have a coldfusion page that I use cfscrpt with some java calls to read an excel file and return it in a query and put into a database. It works like a champ, Unless the main column I need has some fields with numbers and some with letters. For example below.

EXAMPLE 1 ***************************
If the column looks like this:
4569
3659
4569
Self Pay
4569

My Return query will look like this
4569
3659
4569
[empty string]
4569

EXAMPLE 2 ***************************
Now if my column looks like this:
Self Pay
Self Pay
Collection Site
4879
1238

My Query Result looks like:
Self Pay
Self Pay
Collection Site
[empty string]
[empty string]

EXAMPLE 3 ***************************
Excel column is all the same and looks like this:
5983
6321
3684z4
3698i4

The Query result will look like this:
5983
6321
3684z4
3698i4

The code to get the information works, but just the one column is always weird if text fields is mixed with number fields. Seems is goes my the first or first few column fields and if it does not match it returns a empty string value. Any ideas on why this is happening or how to fix it?

The ColdFusion code is as such:

<cffunction name="getExcelSheet" access="public" output="false" returntype="query">
      cfargument name="filename" required="true" type="string" />
      <cfscript>
      var c = "";
      var stmnt = "";
      var rs = "";
      var qSheetNames = getExcelSheetNames(arguments.filename);
      var sql = "Select * from [#qSheetNames.TABLE_NAME#]"; // add error handling
      var myQuery = queryNew("");
      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>

Thanks, CJ
0
Comment
Question by:cj_ervin
  • 7
  • 6
  • 4
17 Comments
 
LVL 11

Accepted Solution

by:
Brijesh Chauhan earned 1000 total points
ID: 37029101
Which version of CF are you using ? if you are on CF 9, then you can use in-build CFSPREADSHEET tag to read excel sheets, if on other versions you can use POI UTILITY to read / write excel sheets...

POI utility can be downloaded from here..

http://www.bennadel.com/projects/poi-utility.htm 

Both the CFSPREADHSEET and POI gives you a query object which you can insert into database.

For the above function, you can try to define the ColumnType for you query as VARCHAR so that it takes all types...

<cffunction name="getExcelSheet" access="public" output="false" returntype="query">
      <cfargument name="filename" required="true" type="string" />
      <cfargument name="columnType" required="no" type="string" default="varchar" />
      <cfscript>
      var c = "";
      var stmnt = "";
      var rs = "";
      var qSheetNames = getExcelSheetNames(arguments.filename);
      var sql = "Select * from [#qSheetNames.TABLE_NAME#]"; // add error handling
      var myQuery = queryNew("","#Arguments.columnType#");
      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

Open in new window

>
0
 

Author Comment

by:cj_ervin
ID: 37031300
The Client is using ColdFusion 8, so I can not use the cfspreadsheet unfortunately.

Does the POI form Ben need Apache? I was under the understanding that it needed apache to work. Is this true?

I will try the code above and report back.
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 37031327
No .. POI from Ben does not need APACHE... You can just download and use it.. nothing else is required...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:cj_ervin
ID: 37031468
Thats good to know, Thanks.

I tried the code above and I get this error:

The list of column names should have the same size as the list of column types.
Size of column names 0 is different from size of column types 1;
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 37031507
Actually, here you are defining the query where the excel data is stored into a query

var myQuery = queryNew("");

it does not define the TYPE, some logic needs to be there which says the TYPE of columns....
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 37031536
Can you also paste code for function - getExcelSheetNames ?
0
 

Author Comment

by:cj_ervin
ID: 37031592
Code for getExcelSheetNames:

<cffunction name="getExcelSheetNames" access="public" output="false" returntype="query">
      <cfargument name="fullFilePath" required="true" type="string" />
      <cfscript>
      var Local = structNew();
      //arguments.fullFilePath = "C:\inetpub\wwwroot\Safety_Controls_Technology\SCCS_Dev\Reports\" & arguments.fullFilePath;
      arguments.fullFilePath = expandPath(arguments.fullFilePath);
      getClass().forName("sun.jdbc.odbc.JdbcOdbcDriver");  
       Local.connectURL = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ="& arguments.fullFilePath;
        Local.conn = CreateObject("java", "java.sql.DriverManager").getConnection( Local.connectURL );
         Local.meta = Local.conn.getMetaData();
          Local.tables = Local.meta.getTables( javacast("null", ""), javacast("null", ""), "%", javacast("null", "") );
           Local.myQuery = CreateObject("java", "coldfusion.sql.QueryTable").init( Local.tables );
           Local.conn.close();
           return Local.myQuery;
</cfscrip>
0
 
LVL 53

Assisted Solution

by:_agx_
_agx_ earned 1000 total points
ID: 37031626
> I was under the understanding that it needed apache to work.

Yes, it needs apache POI to work. But that's not a problem, because it's baked into CF8.

> Any ideas on why this is happening or how to fix it

It's a known issue when using ODBC. Excel spreadsheet's aren't databases. So ODBC has to read a certain number of rows to "guess" the data of you column (text, number, etc...). If the majority are "text" it uses that type, and returns NULL for the rest.  IIRC, you can force it to treat everything as "text". But I think it only works with OLE - not the jdbc:odbc bridge you're using.  Do you have any Access or MS SQL dsn's?
0
 

Author Comment

by:cj_ervin
ID: 37031673
Ok, I got the code working, declared all the columntypes and it still does the same thing as the first post I posted.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 37031749
What code?

> declared all the columntypes and it still does the same thing

Yep.  Like I said, that's not how ODBC determines the column types. It checks the row VALUES, not the Excel column types, when parsing values.  It's a well known issue of working with ODBC. You can either try and hack the jdbc:odbc settings, or use the POIUtility instead.

0
 

Author Comment

by:cj_ervin
ID: 37031759
Thanks _aqx_, I appreciate the feedback. so basically there is no way around this?

Yes I have some MSSQL and MySQL DSN's. I am using them to put the data read from the excel sheet into a database table (Test environment is MySQL and live is MSSQL)
0
 

Author Comment

by:cj_ervin
ID: 37031778
The code I was refering to was the code sample that brijeshchauhan: put in his first post. Sorry, my post got posted below yours and was sort of confusing. Sorry about that.

Ok, I guess I will have to try to figure out POIUtility.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 37031839
>  so basically there is no way around this?

I've read a few articles "claiming" people have gotten around it. Either by hacking the registry or adding extended properties to the connection URL. I'll see if I can find them.  But truthfully, it's just one of the difficulties of reading xls files (along with odbc).  They don't have a predefined columns and data types like a db table does. So a lot of guessing goes on.  It's possible you could make it work with lots of tweaking. But in the long run, I'm guessing using POI utility would be less work.

0
 
LVL 53

Expert Comment

by:_agx_
ID: 37031991
Finally found one of the articles explaining the settings.  This works for MS SQL

1) Change registry setting
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes=Text
2) Add IMEX=1 to openrowset settings

SELECT   *
FROM    OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;IMEX=1;Database=C:\path\myFile.xls', 'SELECT * FROM [Sheet1$]')
0
 
LVL 53

Expert Comment

by:_agx_
ID: 37032036
Note, with openrowset you can actually do the read AND insert in a single cfquery.

INSERT INTO SomeMSTable (Col1, Col2, ...)
SELECT Col1, Col2, ....
FROM      OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;IMEX=1;Database=C:\path\myFile.xls', 'SELECT * FROM [Sheet1$]')
0
 

Author Comment

by:cj_ervin
ID: 37032665
Thanks for the article and feedback aqx.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 37032774
Welcome :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

572 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