FileToQuery( strFile, strDelimiter, blnHeaderRow, strColumnList )


Downloadable Files

filetoquery_demo.zip ( 2,834 Bytes )

FileToQuery() takea a data file and parses it into a query object. The data file must be a character delimited field set with one record per row. By default, the function uses the first row of the text file to get the column names. You can, however, override this and pass in the column names as a comma-delimited list.

  • <cffunction name="FileToQuery" access="public" returntype="query" output="no"
  • hint="Turns a character-delimited data file into a query object.">
  •  
  • <cfargument name="File" type="string" required="yes" />
  • <cfargument name="Delimiter" type="string" required="no" default=" " />
  • <cfargument name="HeaderRow" type="boolean" required="no" default="yes" />
  • <cfargument name="ColumnList" type="string" requied="no" default="" />
  •  
  • <!--- Define the local scope. --->
  • <cfset var LOCAL = StructNew() />
  • <!--- Read in the data file. --->
  • <cffile
  • action="READ"
  • file="#ARGUMENTS.File#"
  • variable="LOCAL.FileData"
  • />
  • <!--- Break the file up into lines. --->
  • <cfset LOCAL.Lines = ListToArray( LOCAL.FileData, "#Chr(13)##Chr(10)#" ) />
  • <!--- Create a data matrix to hold the data fields in. --->
  • <cfset LOCAL.Matrix = ArrayNew( 1 ) />
  • <!--- Loop over the lines to get the field data. --->
  • <cfloop index="LOCAL.LineIndex" from="1" to="#ArrayLen(LOCAL.Lines)#" step="1">
  •  
  • <!---
  • Get the line data. Wrap each field delimiter with spaces to ensure that
  • all fields will come through when parsing.
  • --->
  • <cfset LOCAL.LineData = Replace( LOCAL.Lines[ LOCAL.LineIndex ], ARGUMENTS.Delimiter, (" " & ARGUMENTS.Delimiter & " "), "ALL" ) />
  •  
  • <!--- Convert the line data to an array. --->
  • <cfset LOCAL.Matrix[ LOCAL.LineIndex ] = ListToArray( LOCAL.LineData, ARGUMENTS.Delimiter ) />
  •  
  • <!--- Loop back ofer the matrix data to trim values. This will counteract the extra spaces we put in above. --->
  • <cfloop index="LOCAL.FieldIndex" from="1" to="#ArrayLen(LOCAL.Matrix[ LOCAL.LineIndex ])#" step="1">
  •  
  • <cfset LOCAL.Matrix[ LOCAL.LineIndex ][ LOCAL.FieldIndex ] = Trim(
  • LOCAL.Matrix[ LOCAL.LineIndex ][ LOCAL.FieldIndex ]
  • ) />
  •  
  • </cfloop>
  •  
  • </cfloop>
  •  
  • <!---
  • Check to see if the file has a header row or not. If it doesn't just use the
  • column list that was passed in.
  • --->
  • <cfif ARGUMENTS.HeaderRow>
  •  
  • <!--- The file does have a header row, so use that as the column list. --->
  • <cfset LOCAL.Query = QueryNew( ArrayToList( LOCAL.Matrix[ 1 ], "," ) ) />
  •  
  • <!--- We are going to start on the second row since the first row is NOT data. --->
  • <cfset LOCAL.DataStartRow = 2 />
  •  
  • <!--- Use a pointer to the first matrix for for the column list. --->
  • <cfset LOCAL.HeaderArray = LOCAL.Matrix[ 1 ] />
  •  
  • <cfelse>
  •  
  • <!--- No header row, so use the column list that was passed in. --->
  • <cfset LOCAL.Query = QueryNew( ARGUMENTS.ColumnList ) />
  •  
  • <!--- We are going to start on the first row since the first row is NOT a header row. --->
  • <cfset LOCAL.DataStartRow = 1 />
  •  
  • <!--- Create a header array based not he column list. Strip any spaces around the commas for this conversion. --->
  • <cfset LOCAL.HeaderArray = ListToArray( REReplace( ARGUMENTS.ColumnList, " ?, ?", ",", "ALL" ) ) />
  •  
  • </cfif>
  •  
  • <!--- Now, loop back over the matrix to build the query. --->
  • <cfloop index="LOCAL.RowIndex" from="#LOCAL.DataStartRow#" to="#ArrayLen(LOCAL.Matrix)#" step="1">
  •  
  • <!--- Add a row to the query. --->
  • <cfset QueryAddRow( LOCAL.Query ) />
  •  
  • <!--- Loop over the columns to add them. --->
  • <cfloop index="LOCAL.FieldIndex" from="1" to="#ArrayLen(LOCAL.HeaderArray)#" step="1">
  •  
  • <!--- Add the value from the matrix into the query. --->
  • <cfset LOCAL.Query[ LOCAL.HeaderArray[ LOCAL.FieldIndex ] ][ LOCAL.Query.RecordCount ] = LOCAL.Matrix[ LOCAL.RowIndex ][ LOCAL.FieldIndex ] />
  •  
  • </cfloop>
  •  
  • </cfloop>
  •  
  • <!--- Return the query. --->
  • <cfreturn LOCAL.Query />
  • </cffunction>

Added May 5, 2006 / Updated May 5, 2006


Home   |   Web Log   |   ColdFusion   |   Projects   |   Resume   |   Job Form   |   Search   |   Contact
Epicenter Consulting - Custom Software Solutions for Business Evolution HostMySite.com - The Leader In ColdFusion Hosting