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.
