make sure you close that java connection when you are done with it... On Fri, Apr 18, 2008 at 4:05 PM, Scott Councill < [EMAIL PROTECTED]> wrote:
> This will be a process where the client will upload the SS weekly to the > site and may use a different file name. Yes I am allowing for a LOT of > latitude on their part but this will work if I can just get to the column > names. The suggestion that axunderwood suggested sounds like it might > work. As for the JDBC code I am using, I found this simple little function > that does the job quite nicely: > > > > <cffunction name="getExcelSheet" access="public" > output="true" returntype="any"> > > <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 > [#sheetName#$]"; > > var myQuery = ""; > > > > 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) > > { > > // myQuery > = catch.message; > > } > > } > > return myQuery; > > </cfscript> > > </cffunction> > > > > Just pass it the File Name and Sheet Name and it returns the query object > for you. Pretty simple, eh? The guy is working on a complete CFC that will > have several functions to communicate with Excel. Check him out at > http://www.kylehayes.info/blog/index.cfm/2006/10/12/Creating-a-Query-from-an-Excel-file > As for POI, haven't checked it out yet but have seen it in some of my search > results. If anyone has some good information about this, please let me > know. > > > > *J. Scott Councill * > Software Engineer II > Direct: 404.601.4390 > Cell: 804.267.9555 > [EMAIL PROTECTED] <[EMAIL PROTECTED]> > > *Spunlogic > *Expect More From the Web > www.spunlogic.com <http://www.spunlogic.com/?CMP=EMC-OutlookSig> > > *Looking for insights and tips on all aspects of interactive marketing? > Visit Spunlogic's Blog <http://www.spunlogic.com/blog/?CMP=EMC-OutlookSig>for > a look inside the minds of a leading interactive agency. > * > > > > *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *McTure, > Greg > *Sent:* Friday, April 18, 2008 3:57 PM > > *To:* discussion@acfug.org > *Subject:* RE: [ACFUG Discuss] QoQ question > > > > Hi Scott: > > > > Are you using SQL Server 2005 as a database? If you are, I would consider > using an SSIS (Integration Services or DTS) package where you can set up an > Excel connection very easily in SSIS. You can then map the spreadsheet > columns to a desired database table column (and perform any necessary data > transformations in the process) and import the spreadsheet data into the > database table. Once the data is in the table you can then use SQL to > compare columns and data. Do you have a sample of the spreadsheet? > > > ------------------------------ > > *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *Scott > Councill > *Sent:* Friday, April 18, 2008 2:25 PM > *To:* discussion@acfug.org > *Subject:* RE: [ACFUG Discuss] QoQ question > > > > Thanks to all for comments. I should have been more detailed. Yes, > spaces in field names is very bad. I am working with a process that uploads > an Excel Spreadsheet that is created by a client. Yes, I could go back to > them and tell them "no spaces", but I wanted to try to find a solution that > wouldn't require that. Once the SS has been uploaded, I then create a JDBC > connection to the workbook and read the data in a specific sheet. That > works fine. Now I have a query object that I am trying to join to a SQL > database query to compare values. Yes, I can loop over the SS query and do > lots of separate queries but I wanted to create a single query solution with > a JOIN statement. > > > > So the answer seems to be NO QoQ does not support column names with > spaces, not even using the proper "[]" bracket syntax. Is this correct? > > > > *J. Scott Councill * > Software Engineer II > Direct: 404.601.4390 > Cell: 804.267.9555 > [EMAIL PROTECTED] <[EMAIL PROTECTED]> > > *Spunlogic > *Expect More From the Web > www.spunlogic.com <http://www.spunlogic.com/?CMP=EMC-OutlookSig> > > *Looking for insights and tips on all aspects of interactive marketing? > Visit Spunlogic's Blog <http://www.spunlogic.com/blog/?CMP=EMC-OutlookSig>for > a look inside the minds of a leading interactive agency. > * > > > > *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *Cameron > Childress > *Sent:* Friday, April 18, 2008 9:19 AM > *To:* discussion@acfug.org > *Subject:* Re: [ACFUG Discuss] QoQ question > > > > To be fair, QoQ solves an entirely different problem than Stored Procs. > QoQ was also not designed to be an entire DB server, and hopefully never > will be. > > QoQ is quirky as hell, yes, but for the newbies out there on the list it > would be wrong (IMHO) to make a blanket recommendation about "using stored > procedures or functions instead of QoQ" without pointing out that they solve > entirely different problems, and really the question of which (SP or QoQ) > you should use would hopefully have been asked an answered way before this > point in Scott's development (which I'm sure it was). > > -Cameron > > On Fri, Apr 18, 2008 at 9:03 AM, Tommy Geist <[EMAIL PROTECTED]> > wrote: > > If I may offer some CONSTRUCTIVE answers like John, > > Query of Queries are very limited in their abilities > compared to what modern databases will do. I believe > they are even behind the basic mySQL so I would > recommend using stored procedures or functions instead > of QoQ. > > > > -- > Cameron Childress > Sumo Consulting Inc > http://www.sumoc.com > --- > cell: 678.637.5072 > aim: cameroncf > email: [EMAIL PROTECTED] > ------------------------------------------------------------- > Annual Sponsor - Figleaf Software <http://www.figleaf.com> > > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- > > > ------------------------------------------------------------- > Annual Sponsor - Figleaf Software <http://www.figleaf.com> > > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- > ------------------------------------------------------------- > Annual Sponsor - Figleaf Software <http://www.figleaf.com> > > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- > > ------------------------------------------------------------- > Annual Sponsor - Figleaf Software <http://www.figleaf.com> > > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- > -- Steven Ross web application & interface developer http://blog.stevensross.com [mobile] 404-488-4364 [fax] (404) 592-6885 [ AIM / Yahoo! : zeriumsteven ] [googleTalk : nowhiding ] ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------