Scott,
One last suggestion - why not save the data into a new query object with the
fixed column names and then use the new query:
<cfquery name="qryTest" datasource="mydsn">
select top 10
[first name],
[last name]
from some_table
</cfquery>
<cfset newQuery=QueryNew(Replace(qryTest.columnList," ", "_", "ALL")) />
<cfset newQuery = QueryNew("")>
<cfloop from="1" to="#ListLen(qryTest.columnList)#" index="i">
<cfset dataArray =
ListToArray(ArrayToList(qryTest[ListGetAt(qryTest.columnList, i)]))>
<cfset QueryAddColumn(newQuery, ReplaceNoCase(ListGetAt(qryTest.columnList,
i)," ", "_", "ALL"), "varchar", dataArray) />
</cfloop>
<cfdump var="#qryTest#">
<cfdump var="#newQuery#">
________________________________
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Howard Fore
Sent: Friday, April 18, 2008 2:52 PM
To: [email protected]
Subject: Re: [ACFUG Discuss] QoQ question
Scott,
Have you thought about using the POI stuff to read the Excel sheet? There's a
lot of info on a CFC implementation at
http://www.bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Read-And-Write-Excel-Files.htm.
True, that info then comes in as a struct but you could loop over the struct
and create query without the spaces in the column names as you need for QOQ.
Just a thought...
Howard
On Fri, Apr 18, 2008 at 2:25 PM, Scott Councill <[EMAIL
PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
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?
--
Howard Fore, [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
"The universe tends toward maximum irony. Don't push it." - Jeff Atwood
-------------------------------------------------------------
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
-------------------------------------------------------------
