Description
Latest Version: 1.0.5 (Released: 03/19/2005)

Java-based CFX tag for parsing delimited text files and converting them to ColdFusion queries. This is particularly useful for CSV (comma-separated) files which are commonly used for data imports.

CFX_Text2Query works better than other methods because it can handle empty fields and fields with line breaks, and it does it at breakneck speeds!

One alternative, using CFHTTP, requires the file to be web-accessible, and cannot handle empty fields OR line breaks. This often results in the "Incorrect number of columns in row." error and a lot of headaches.

Other methods involving native CFML are slow and cumbersome and rarely account for line breaks.

cfx_text2query, as far as i can tell, handles more files, better, than the alternatives. This tag is a version 1 release, though, so incompatibilities are not impossible. Thus far, however, i have yet to uncover any.

Compatibility:
CF4.5,CF5,CFMX, CFMX7 - Linux/Unix,Windows
 Installation
New! Installation Guide
 Usage
<cfx_text2Query
      file="#expandPath('.')#\test.csv"
      firstRowIsHeader="false"
      columnNames="CustomerID,FirstName,LastName,Address,City,State,Zip"
      qualifier="#chr(34)#"
      delimiter=","
      maxrows=25
      startrow=1
      rQuery="results">


 Attributes
Attribute Req'd Description
File Yes The file to process
rQuery Yes The name of the resultant query
Delimiter No The field separator (Default: comma (,))
Qualifier No The text qualifier (Default: double quote (
StartRow No The row in the text file from which to start (Default: 1)
MaxRows No The maximum number of rows to return (Default: infinite)
FirstRowIsHeader No Set to 'true' if the column names can be retrieved from the first row of the text file (Default: false)
ColumnNames No A comma-separated list of column names (Default: column1,column2,column3,..,columnN)
LongNames No Use long names for dynamic column names (Default: false)
skipLinesStartingWith No Skip any lines that start with this string. Useful when text files contain comments.
unicode No Set to true to process the file as unicode (Default: false)


 Notes
############################################
Notes
############################################
If firstRowIsHeader is set to false, cfx_text2query will dynamically generate column names in the form:

column1,column2,column3,..,columnN

If you like, you can set longnames="true" to have the column names generated as:

column001,column002,column003,..,columnNNN

If you know the names of the columns and they are not present in the text file, you can statically assign them using the columnNames attribute. This is a comma-delimited list of column names. If there are not enough column names specified, the above, dynamic, format is used to fill in the gaps. If too many are specified, any extra names are ignored.

Lastly, if the first row of the file contains the column names, you can set firstrowisheader to "true" to have the names assigned from the file. In this case, there must be a valid column name for each column in the file!

############################################
Known Issues
############################################
None.