– Common Function Library Project

fixedWidthToQuery(columnNames, widths, data[, customRegex])

Last updated December 20, 2007


Umer Farooq

Version: 1 | Requires: CF6 | Library: DataManipulationLib

Converts any type of fixed width string to a ColdFusion query object.

Return Values:
Returns a query.


<cffile action="READ" file="50102-new.txt" variable="fileData"></cffile>
<cfset columNames = "stockNo,year,make,model,vehicleTrim,extcolor,vin,price">
<cfset columnWidth = "11,5,16,16,21,16,19,8">
<cfset recordSet = fixedWidthToQuery(columNames,columnWidth,fileData)>


Name Description Required
columnNames A list of column names. Yes
widths A corresponding list of widths. Yes
data The data to parse. Yes
customRegex A regular expression to be used to parse the line. No

Full UDF Source:

 Converts fixed width string to a ColdFusion query.
 Modified by Raymond Camden for missing var, and support newlines better.
 @param columnNames 	 A list of column names. (Required)
 @param widths 	 A corresponding list of widths. (Required)
 @param data 	 The data to parse. (Required)
 @param customRegex 	 A regular expression to be used to parse the line. (Optional)
 @return Returns a query. 
 @author Umer Farooq ( 
 @version 1, December 20, 2007 
<cffunction name="fixedWidthToQuery" hint="I turn fixed width data to query">
	<cfargument name="columnNames" required="Yes" type="string">
	<cfargument name="widths" required="Yes" type="string">
	<cfargument name="data" required="Yes" type="string">
	<cfargument name="customRegex" required="No" type="string">
	<cfset var tempQuery = QueryNew(arguments.columnNames)>
	<cfset var regEx = "">
	<cfset var findResults = "">
	<cfset var i = "">
	<cfset var line = "">
	<cfset var x = "">
	<!--- build our regex --->
	<cfif NOT isDefined("arguments.customRegEx")>
		<cfloop list="#arguments.widths#" index="i">
			<cfset regex = regex & "(.{" & i & "})">
		<cfset regEx = arguments.customRegex>
	<!--- fix newlines for different os --->
	<cfset = replace(,chr(10),chr(13),"all")>
	<cfset = replace(,chr(13)&chr(13),chr(13),"all")>
	<!--- loop the data --->
	<cfloop list="" delimiters="#chr(13)#" index="line">
		<!--- run our regex --->
		<cfset findResults = refind(regEx, line, 1, true)>
		<!--- find our that our match records equals number of columns plus one. --->
		<cfif arrayLen(findResults.pos) eq listLen(arguments.columnNames)+1>
			<cfset QueryAddRow(tempQuery)>
			<!--- loop the find resuls array from postion 2... 
			      and get the column name x-1 as our regex results are number of columsn plus 1
				  and load that data into the query  --->
			<cfloop from="2" to="#arrayLen(findResults.pos)#" index="x">
				<cfset QuerySetCell(tempQuery, listGetAt(arguments.columnNames, x-1), trim(mid(line, findResults.pos[x], findResults.len[x])))> 
	<cfreturn tempQuery>
blog comments powered by Disqus


Latest Additions

Kevin Cotton added
May 5, 2016

Raymond Camden added
April 25, 2016

Chris Wigginton added
January 18, 2016

Gary Stanton added
November 19, 2015

Sebastiaan Naafs - van Dijk added
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson