CF_QueryToText v1.1 Tag Documentation

tag written by Roy P. Johnston, Jr.
Created October 8, 1998 - Updated Friday, February 28, 2003

The most current version of this document is available at: http://www.johnstons.org/wdc/pub/cfusion/customtags/CF_QueryToText.html

Contents

Description

CF_QueryToText processes the results of a Cold Fusion query into a flat text format suitable for data exporting. The results can be save as a file or returned in a variable. Look in the Cold Fusion documention for instructions on how to install this tag.

An Unencrypted version of this tag is available upon request.

Attributes

AttributeTypeDescription
QUERYNAME="name" Required Specifies the name of the query to be processed. The query should already be executed before calling the CF_QueryToText tag.
Action="[ReturnOnly | WriteToFile]" Required Specifies the how the results are returned. ReturnOnly will return the results in the variable QTT_QueryToText. WriteToFile will also return the results in QTT_QueryToText but will also write the results to a file as specified in the TextFile parameter. If Action is not specified, it will default to ReturnOnly.
TextDelimiter="character" Required Delimiter used for resulting columns. If TextDelimiter is not specified, Comma (,) is used as the default.
TextQualifier="character" Optional The qualifier used for the resulting data cells. Quote (") is the default. An empty qualifier may be specified (TextQualifier="").
TextFile="file path" Optional Specifies the file to which to output the results. If a directory is specified without a file name, QueryToText.txt will be the file name used by default. This parameter is only valid when ACTION="WriteToFile" and is required for this action.
ColumnHeaders="[Yes | No]" Optional Indicates if column headers should be returned in the result. Yes is the default.
ColumnsIncluded="comma delimited list" Optional Indicates what columns for the query should be returned in the result. Also specifies in what order the columns will appear. If not specified, all columns from the query are returned in the order given in the query.
ColumnTypes="comma delimited list" Optional Used in conjunction with ColumnsIncluded to specify alternate formatting for column data. ColumnTypes is a delimited list of these format tokens :

TEXT, DATE, TIME, DATETIME, CURRENCY, NUMBER

If not specified, TEXT formatting is used. Only TEXT formatting will escape qualifiers in the datum and enclose the datum in the specified qualifier. Requires a 1 to 1 correspondence with the ColumnsIncluded list.

ColumnHeaderNames="comma delimited list" Optional Used in conjunction with ColumnsIncluded to specify alternate column names for output purposes. For example, the column ID can be shown in the results as Key. Requires that ColumnHeaders=Yes and a 1 to 1 correspondence with the ColumnsIncluded list.
InfoLine="[Yes | No]" Optional Will append an information line to the top of the results. Includes date processed, the number or records returned and optional InfoText comments. No is the default.
InfoText="string" Optional Adds comments to the information line of the results. Requires InfoLine=Yes.

Returned Values

VariableDescription
QTT_QueryToText The results of the processed query in flat text format.
QTT_ErrorCode Numeric error code that corresponds with the descriptive QTT_ErrorMessage.
QTT_ErrorMessage Error message

Error Values

Code	Message
0      	OK.
1	The specified ACTION is not valid.  Use ReturnOnly or WriteToFile.
2	The required parameter QUERYNAME was not specified.
3	The QUERYNAME specified is not valid or the specified query has not executed yet.
4	A TEXTDELIMITER is required and must be only one character.
5	A TEXTDELIMITER cannot be a CR or LF value.
6	A TEXTQUALIFIER must be only one character.
7	A TEXTQUALIFIER cannot be a CR or LF value.
8	The number of COLUMNHEADERNAMES does not match the number of COLUMNSINCLUDED.
9	COLUMNHEADERNAMES are ignored because the parameter COLUMNSINCLUDED is not specified correctly.
10	Cannot write results to file.  The parameter TEXTFILE is not specified.
11	Cannot write results to file.  The directory path specified in TEXTFILE does not exist.
12	One or more columns in COLUMNSINCLUDED are not found in the specified QUERYNAME query.
13	The number of COLUMNTYPES does not match the number of COLUMNSINCLUDED.
14	COLUMNTYPES are ignored because the paramter COLUMNSINCLUDED is not specified correctly.
15	The parameter COLUMNTYPES contains ignored token(s).  Token(s) are not valid.

Syntax

Simple Form: 

<CF_QueryToText QueryName="MyQuery">

Advanced Form: 

<CF_QueryToText QueryName="MyQuery"
	ACTION="WriteToFile" TextFile="c:\somepath\somefile.txt"
	TextQualifier="" TextDelimiter=";"
	InfoLine="Yes" InfoText="Sample Report"
	ColumnsIncluded="ReportID,ReportDate,Conversion"
	ColumnHeaderNames="Key,Date of Report,C Rate"
	ColumnTypes="NUMBER,DATE,NUMBER">

Sample Code

First populate MyQuery. Then execute CF_QueryToText. 

<CFQUERY NAME="MyQuery" DATASOURCE="MyDatasource">
	SELECT TOP 5 ReportID, ReportDate, Conversion FROM WeeklySalesReports
</CFQUERY>

<CF_QueryToText QueryName="MyQuery"
	ACTION="WriteToFile" TextFile="c:\somepath\somefile.txt"
	TextQualifier="" TextDelimiter=";"
	InfoLine="Yes" InfoText="Sample Report"
	ColumnsIncluded="ReportID,ReportDate,Conversion"
	ColumnHeaderNames="Key,Date of Report,C Rate">

Returned values:

QTT_ErrorCode = 0
QTT_ErrorMessage = OK.
QTT_QueryToText = 

-- Extracted {ts '1998-10-09 10:04:43'} -- 5 records -- (Sample Report)
Key;Date of Report;C Rate
1;1998-02-28 00:00:00;0.0285307
2;1998-02-28 00:00:00;1.0
3;1998-02-28 00:00:00;0.703433
4;1998-02-28 00:00:00;0.703433
5;1998-02-28 00:00:00;0.703433

In addition the file somefile.txt will be created in the directory c:\somepath\. This file will contain the same contents as QTT_QueryToText.

FAQ

Qusetion: How can I get <CF_QueryToText> to save output to the user’s hard drive?

Answer: <CF_QueryToText> allows you to save output locally (on the server). You then will use something like <CFCONTENT> to push the file to the client.

Example: I have a template that uses <CF_QueryToText> to generate a *.csv file. At the bottom of that page I have a form button that links to a file with this one line:

<CFCONTENT TYPE="application/ms-excel" FILE="#ExpandPath('.')#\marketing.csv">

The form button looks like this:

<FORM ACTION="reportxl2.cfm/marketing.csv" METHOD="POST">
<INPUT TYPE="Submit" VALUE="Get Spreadsheet">
</FORM>

Notice the form action. It will actually submit to the template reportxl2.cfm, but the "/marketing.csv" is necessary so that file will be saved with that name (otherwise it would be saved as reportxl2.cfm).

Package Information & Download

Support

Tech support can be obtained at: http://www.johnstons.org/wdc/pub/cfusion/support.cfm.

Send suggestions or comments to ihuj@yahoo.com. Submit bug reports or code modifications to ihuj@yahoo.com with the subject line "CF_QueryToText".

Version History

1.1
Released 1/8/99 - Features:

1.0
Released 10/8/98 - Features: