Creating a Query from a Text File  
 
 

Using the CFHTTP Get operation, you can create a query object from a delimited text file. This is a powerful means for processing and handling generated text files. Once the query object is created, it is very simple to reference columns in the query and perform other ColdFusion magic on the data.

Text files are processed in the following manner:

  • You specify a delimiter with the DELIMITER attribute. If data in a field includes the delimiter character, it must be quoted or qualified with some other character, which you specify with the TEXTQUALIFIER attribute.
  • By default, the first row of the text file is interpreted as column heading text. You can replace this heading text by specifying alternate heading text in the COLUMNS attribute, making sure that you supply an alternate for every column of data in the text file.
  • 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_".
 
 
  Example: Creating a query from a text file  
 
 

In this example, a query object is created from a comma-delimited text file. The CFOUTPUT tag is used to output specific columns in the query. The text file consists of six columns, separated by commas. The first row of the file looks like this:

OrderID,OrderNum,OrderDate,ShipDate,ShipName,ShipAddress

This example accepts the first row of the text file as the column names:

<CFHTTP METHOD="Get"
    URL="http://127.0.0.1/orders/june/orders.txt"
    NAME="juneorders"
    DELIMITER=","
    TEXTQUALIFIER="""">

<CFOUTPUT QUERY="juneorders">
    OrderID: #OrderID#<BR>
    Order Number: #OrderNum#<BR>
    Order Date: #OrderDate#<BR>
</CFOUTPUT>

You can substitute different column names by using the COLUMNS attribute:

<CFHTTP METHOD="Get"
    URL="http://127.0.0.1/orders/june/orders.txt"
    NAME="juneorders"

    COLUMNS="ID, Number,Date"
    DELIMITER=","
    TEXTQUALIFIER="""">

<CFOUTPUT QUERY="juneorders">
    Order ID: #ID#<BR>
    Order Number: #Number#<BR>
    Order Date: #Date#<BR>
</CFOUTPUT>


 
 
BackUp LevelNext
 
 

allaire     AllaireDoc@allaire.com
    Copyright © 1998, Allaire Corporation. All rights reserved.