CFLib.org – Common Function Library Project

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

Last updated September 30, 2005
Download UDF

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

Simon Bingham added
convertSecondsTo...
4 months ago

Umbrae added
fullUrlEncode
6 months ago

Mosh Teitelbaum added
minutesToUtcOffs...
7 months ago

Mosh Teitelbaum added
utcOffsetToMinut...
7 months ago

Hank van Empel added
validateIBAN
7 months ago

Created by Raymond Camden / Design by Justin Johnson