CFLib.org – fixedWidthToQuery

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

Last updated December 20, 2007

Download UDF

author

Umer Farooq
Send Email

Version: 1 | Requires: ColdFusion MX | Library: DataManipulationLib

Rated 1 time(s). Average Rating: 4.0

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

Return Values:
Returns a query.

Example:

view plain print about

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

Parameters:

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:

	
	
			
			
			
				
				view plain
				
				
				copy to clipboard
				
				
				
				
				print
				
				
				about
				ColdFISH is developed by Jason Delmore.  Source code and license information available at coldfish.riaforge.org.  Version 3.1.1
				
			<!---
 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 (&#117;&#109;&#101;&#114;&#64;&#111;&#99;&#116;&#97;&#100;&#121;&#110;&#101;&#46;&#99;&#111;&#109;) 
 @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 & "})">
		</cfloop>
	<cfelse>
		<cfset regEx = arguments.customRegex>
	</cfif>
	
	<!--- fix newlines for different os --->
	<cfset arguments.data = replace(arguments.data,chr(10),chr(13),"all")>
	<cfset arguments.data = replace(arguments.data,chr(13)&chr(13),chr(13),"all")>
	
	<!--- loop the data --->
	<cfloop list="#arguments.data#" 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])))> 
			</cfloop>
		</cfif>
	</cfloop>
	<cfreturn tempQuery>
</cffunction>
		<!---
 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 (umer@octadyne.com) 
 @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 & "})">
        </cfloop>
    <cfelse>
        <cfset regEx = arguments.customRegex>
    </cfif>
    
    <!--- fix newlines for different os --->
    <cfset arguments.data = replace(arguments.data,chr(10),chr(13),"all")>
    <cfset arguments.data = replace(arguments.data,chr(13)&chr(13),chr(13),"all")>
    
    <!--- loop the data --->
    <cfloop list="#arguments.data#" 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])))> 
            </cfloop>
        </cfif>
    </cfloop>
    <cfreturn tempQuery>
</cffunction>