query result to ASCII file output

[auntiealias]

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!!!!! :)

[Dan_Bracuk]

perhaps the columnlist variable of cfquery might be of use.

Details are in the cfml reference manual or livedocs. Search on <cfquery>

[JR__Bob__Dobbs-qSBHQ2]

http://labs.redbd.net/projects/csvLibrary.cfm

[auntiealias]

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__-]

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

[auntiealias]

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.

[-__cfSearching__-]

auntiealias
> I tried that, but as far as I could figure you still have to have a specific column name

Look at the udf more closely. If you read the documentation it states all of the parameters are optional, except the query. So if you do not supply column names, the function will use the columnList variable to determine the names of the columns in the query. If you look at the actual code, notice it uses array notation to extract the values (not evaluate) .

... (code to create query) ...
<!--- pass in the query object only ---->
<cfset test = QueryToCSV2( data )>

http://cflib.org/index.cfm?event=page.udfbyid&udfid=1197

> I have a cfselect with multiple set to "yes" and the selections are "which columns of
> data do you want to see?"

Hopefully you are not using the raw form value directly in your sql, because that is a sql injection attack just waiting to happen.