CFLib.org – Common Function Library Project

QuerySim(queryData)

Last updated December 18, 2007

author

Bert Dawson

Version: 2 | Requires: CF5 | Library: DataManipulationLib

Description:
Accepts a specifically formatted chunk of text, and returns it as a query object. Based on QuerySim.cfm by hal.helms@TeamAllaire.com. Pass in a block of text where the first line is a comma separated column list, and subequent lines represent records, with '|' delimited data cells. Note that version 2 no longer accepts the queryname as the first line.

Return Values:
Returns a query object.

Example:

<cfscript>
people = querySim('
id , name , mail
1 | weed | weed@theflowerpot.not
2 | bill | bill@theflowerpot.not
3 | ben | ben@theflowerpot.not
');
</cfscript>

People
<cfdump var="#people#">

Parameters:

Name Description Required
queryData Specifically format chunk of text to convert to a query. Yes

Full UDF Source:

/**
 * Accepts a specifically formatted chunk of text, and returns it as a query object.
 * v2 rewrite by Jamie Jackson
 * 
 * @param queryData 	 Specifically format chunk of text to convert to a query. (Required)
 * @return Returns a query object. 
 * @author Bert Dawson (bert@redbanner.com) 
 * @version 2, December 18, 2007 
 */
function querySim(queryData) {
	var fieldsDelimiter="|";
	var colnamesDelimiter=",";
	var listOfColumns="";
	var tmpQuery="";
	var numLines="";
	var cellValue="";
	var cellValues="";
	var colName="";
	var lineDelimiter=chr(10) & chr(13);
	var lineNum=0;
	var colPosition=0;

	// the first line is the column list, eg "column1,column2,column3"
	listOfColumns = Trim(ListGetAt(queryData, 1, lineDelimiter));
	
	// create a temporary Query
	tmpQuery = QueryNew(listOfColumns);

	// the number of lines in the queryData
	numLines = ListLen(queryData, lineDelimiter);
	
	// loop though the queryData starting at the second line
	for(lineNum=2;  lineNum LTE numLines;  lineNum = lineNum + 1) {
	    cellValues = ListGetAt(queryData, lineNum, lineDelimiter);

		if (ListLen(cellValues, fieldsDelimiter) IS ListLen(listOfColumns,",")) {
			QueryAddRow(tmpQuery);
			for (colPosition=1; colPosition LTE ListLen(listOfColumns); colPosition = colPosition + 1){
				cellValue = Trim(ListGetAt(cellValues, colPosition, fieldsDelimiter));
				colName   = Trim(ListGetAt(listOfColumns,colPosition));
				QuerySetCell(tmpQuery, colName, cellValue);
			}
		} 
	}
	
	return( tmpQuery );
	
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson