0
query result to ASCII file output
New Here
,
/t5/coldfusion-discussions/query-result-to-ascii-file-output/td-p/862401
Oct 09, 2008
Oct 09, 2008
Copy link to clipboard
Copied
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!!!!! :)
TOPICS
Advanced techniques
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
LEGEND
,
/t5/coldfusion-discussions/query-result-to-ascii-file-output/m-p/862402#M79542
Oct 09, 2008
Oct 09, 2008
Copy link to clipboard
Copied
perhaps the columnlist variable of cfquery might be of use.
Details are in the cfml reference manual or livedocs. Search on <cfquery>
Details are in the cfml reference manual or livedocs. Search on <cfquery>
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Advisor
,
/t5/coldfusion-discussions/query-result-to-ascii-file-output/m-p/862403#M79543
Oct 09, 2008
Oct 09, 2008
Copy link to clipboard
Copied
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
auntiealias
AUTHOR
New Here
,
/t5/coldfusion-discussions/query-result-to-ascii-file-output/m-p/862404#M79544
Oct 12, 2008
Oct 12, 2008
Copy link to clipboard
Copied
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!
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Valorous Hero
,
/t5/coldfusion-discussions/query-result-to-ascii-file-output/m-p/862405#M79545
Oct 13, 2008
Oct 13, 2008
Copy link to clipboard
Copied
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
> 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
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
auntiealias
AUTHOR
New Here
,
/t5/coldfusion-discussions/query-result-to-ascii-file-output/m-p/862406#M79546
Oct 14, 2008
Oct 14, 2008
Copy link to clipboard
Copied
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Valorous Hero
,
LATEST
/t5/coldfusion-discussions/query-result-to-ascii-file-output/m-p/862407#M79547
Oct 14, 2008
Oct 14, 2008
Copy link to clipboard
Copied
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.
> 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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

