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:* [email protected]
> *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:* [email protected]
> *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:* [email protected]
> *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
-------------------------------------------------------------
