– 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>


Latest Additions

Raymond Camden added
November 04, 2017

Leigh added
May 11, 2016

Raymond Camden added
May 10, 2016

Kevin Cotton added
May 05, 2016

Raymond Camden added
April 25, 2016

Created by Raymond Camden / Design by Justin Johnson