User Guide
Creating a Query from a Text File 335
Creating a Query from a Text File
You can create a query object from a delimited text file by using the cfhttp tag and
specifying
method="Get" and the name attribute. This is a powerful method for
processing and handling generated text files. After you create the query object, you
can easily reference columns in the query and perform other ColdFusion operations
on the data.
ColdFusion processes text files in the following manner:
• You can specify a field delimiter with the
delimiter attribute. The default is a
comma.
• If data in a field might include the delimiter character, you must surround the
entire field with the text qualifier character, which you can specify with the
textqualifier attribute. The default text qualifier is the double quotation mark
(").
•
textqualifier="" specifies that there is no text qualifier. textqualifier=""""
(four " marks in a row) explicitly specifies the double quotation mark as the text
qualifier.
• If there is a text qualifier, you must surround all field values with the text qualifier
character.
• To include the text qualifier character in a field, use a double character. For
example, if the text qualifier is ", use "" to include a quotation mark in the field.
• The first row of text is always interpreted as column headings, so that row is
skipped. You can override the file’s column heading names by specifying a
different set of names in the
columns attribute. You must specify a name for each
column. You then use these new names in your CFML code. However,
ColdFusion never treats the first row of the file as data.
• When duplicate column heading names are encountered, ColdFusion adds an
underscore character to the duplicate column name to make it unique. For
example, if two CustomerID columns are found, the second is renamed
"CustomerID_".
To create a query from a text file:
1 Create a new file in ColdFusion Studio.
2 Modify the file so that it appears as follows:
<!--- The text file has six columns separated by commas: --->
<!--- OrderID,OrderNum,OrderDate,ShipDate,ShipName,ShipAddress --->
<!--- This example uses the first row as the column names --->
<cfhttp method="Get"
url="http://127.0.0.1/orders/june/orders.txt"
name="juneorders">
<cfoutput query="juneorders">
OrderID: #OrderID#<br>
Order Number: #OrderNum#<br>
Order Date: #OrderDate#<br>
</cfoutput>