Since the thread has moved from discussion of Q or Q to Excel file processing options, I'll share that this is another of the 40+ categories in my list of 400+ tools, to answer the "how do I ." questions. Check out:
Excel File Processing Tools http://www.carehart.org/resourcelists/tools_to_consider/index.cfm#excel I discuss several alternatives, including the many Java APIs, as well as COM, CSV, HTML Table, Java, .NET, Office Automation, ODBC/JDBC, Report Builder, WebQueries, and XML. /charlie From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Councill Sent: Friday, April 18, 2008 4:05 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] QoQ question 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:Drive r={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 <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] Spunlogic Expect More From the Web <http://www.spunlogic.com/?CMP=EMC-OutlookSig> www.spunlogic.com Looking for insights and tips on all aspects of interactive marketing? Visit <http://www.spunlogic.com/blog/?CMP=EMC-OutlookSig> Spunlogic's Blog 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 <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] Spunlogic Expect More From the Web <http://www.spunlogic.com/?CMP=EMC-OutlookSig> www.spunlogic.com Looking for insights and tips on all aspects of interactive marketing? Visit <http://www.spunlogic.com/blog/?CMP=EMC-OutlookSig> Spunlogic's Blog 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> ------------------------------------------------------------- ------------------------------------------------------------- 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 -------------------------------------------------------------
