Skip to main content
A
auntiealias
Participant
October 9, 2008
Question

query result to ASCII file output

  • October 9, 2008
  • 2 replies
  • 706 views
Seems like there SHOULD be some rediculously simple solution to this - but I sure haven't found it yet! I have a query which produces the a varying number of columns (based on a multi-select cfselect) which I need to output to a comma delimited ASCII (.TXT or .DAT or .CSV file). I have all the steps worked out when the number of columns and their names are known, but I need to make this variable. First choice would be to be able to just "DUMP" the query result into a text file. CFDUMP can send results to a text file, but NOT in comma delimited format, and with all kinds of info besides just the basic data. (NOT good!) Second choice, since the "append to text file" uses an output property that expects a comma delimited string variable would be to use CFLOOP to loop thru the rows in the query and put them (a row at a time) into a variable, then use that variable for the file append. Problem is I can't find a way to just say "send *ALL* to the string variable. Every example I have been able to find includes specifying the column NAMES when doing anything with a row in the query result!!! Short of a brute force series of if/then statements that specify EACH POSSIBILITY (in this case 60 - 75 of them, and the number WILL GROW!) I haven't been able to find a solution. There's GOTTA BE A WAY!!!!! :)
This topic has been closed for replies.

2 replies

A
auntiealiasAuthor
Participant
October 13, 2008
Thanks both to Dan Bracuk and Bob Dobbs, but the problem I was having was to get the VALUE of the variable represented by the column names, not the column names themselves. I already have that, but WHICH column are in the query depends on the selections made. The usual situation is for a person to select items IN A SPECIFIC COLUMN (like "color" or "name" column). Everything I found required me to specify the field name, which itself is a variable. Got the help I needed on the DaniWeb Forum. FYI, it was the "evaluate" function that did the trick. Thanks anyway!
-
-__cfSearching__-
Inspiring
October 13, 2008
auntiealias wrote:
> but the problem I was having was to get the VALUE of the variable represented by the column
> names, not the column names themselves ...
> FYI, it was the "evaluate" function that did the trick.

The evaluate function is not needed. To retrieve a column value just use associative array notation.

#yourQueryName["yourColumnName"][rowNumber]#

You can view an example in the QueryToCSV2 function at cflib.org. It uses the columnList variable (mentioned earlier) with array notation to retrieve the actual column values:

http://cflib.org/index.cfm?event=page.udfbyid&udfid=1197
A
auntiealiasAuthor
Participant
October 15, 2008
I tried that, but as far as I could figure you still have to have a specific column name, not a variable which contains the colum name in the ["yourColumnName"] part. I have a cfselect with multiple set to "yes" and the selections are "which columns of data do you want to see?" The resulting variable is a comma delimited string. I then need to use that string variable to display the results (the VALUES) ONLY OF THE COLUMNS SELECTED. The evaluate function is the only thing that seems to do this.
D
Dan_Bracuk
Inspiring
October 9, 2008
perhaps the columnlist variable of cfquery might be of use.

Details are in the cfml reference manual or livedocs. Search on <cfquery>
J
JR__Bob__Dobbs-qSBHQ2
Inspiring
October 9, 2008
http://labs.redbd.net/projects/csvLibrary.cfm
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices