CFLib.org – Common Function Library Project

CSVToQuery(cvsString[, rowDelim][, colDelim])

Last updated September 30, 2005

author

Tony Brandner

Version: 1 | Requires: CF5 | Library: DataManipulationLib

Description:
Takes a CSV (comma separated values) formatted string with option row and column delimiters and transforms into a query object. The first row of the CSV string must contain the column headers.

Return Values:
Returns a query.

Example:

<cfsavecontent variable="newCSV">col1,col2,col3
row1val1,row1val2
row2val1,row2val2,row2val3
</cfsavecontent>

<cfdump var="#CSVToQuery(newCSV)#">

Parameters:

Name Description Required
cvsString CVS Data. Yes
rowDelim Row delimiter. Defaults to CHR(10). No
colDelim Column delimiter. Defaults to a comma. No

Full UDF Source:

/**
 * Transform a CSV formatted string with header column into a query object.
 * 
 * @param cvsString 	 CVS Data. (Required)
 * @param rowDelim 	 Row delimiter. Defaults to CHR(10). (Optional)
 * @param colDelim 	 Column delimiter. Defaults to a comma. (Optional)
 * @return Returns a query. 
 * @author Tony Brandner (tony@brandners.com) 
 * @version 1, September 30, 2005 
 */
function csvToQuery(csvString){
	var rowDelim = chr(10);
	var colDelim = ",";
	var numCols = 1;
	var newQuery = QueryNew("");
	var arrayCol = ArrayNew(1);
	var i = 1;
	var j = 1;
	
	csvString = trim(csvString);
	
	if(arrayLen(arguments) GE 2) rowDelim = arguments[2];
	if(arrayLen(arguments) GE 3) colDelim = arguments[3];

	arrayCol = listToArray(listFirst(csvString,rowDelim),colDelim);
	
	for(i=1; i le arrayLen(arrayCol); i=i+1) queryAddColumn(newQuery, arrayCol[i], ArrayNew(1));
	
	for(i=2; i le listLen(csvString,rowDelim); i=i+1) {
		queryAddRow(newQuery);
		for(j=1; j le arrayLen(arrayCol); j=j+1) {
			if(listLen(listGetAt(csvString,i,rowDelim),colDelim) ge j) {
				querySetCell(newQuery, arrayCol[j],listGetAt(listGetAt(csvString,i,rowDelim),j,colDelim), i-1);
			}
		}
	}
	return newQuery;
}
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