CFLib.org – Common Function Library Project

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

Last updated September 30, 2005

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

 
Rated 7 time(s). Average Rating: 3.1

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:

view plain print about
<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:

view plain print about
<cfscript>
/**
 * 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;
}
</cfscript>
blog comments powered by Disqus

Search CFLib.org


Latest Additions

CF Ninja CF Ninja added
calcIRR
10 day(s) ago

Stephen Withington Stephen Withington added
getWeekOfMonth
10 day(s) ago

Adam Cameron Adam Cameron added
createPrimeNumbe...
a while ago

Ray Ford Ray Ford added
timeZoneNow
a while ago

Top Rated

Darwan Leonardo Sitepu backupDatabase
Rated 5.0, 48 time(s)

Barney Boisvert indentXml
Rated 5.0, 12 time(s)

Rachel Lehman deAccent
Rated 5.0, 9 time(s)

Darwan Leonardo Sitepu splitNumber
Rated 5.0, 8 time(s)

Created by Raymond Camden / Design by Justin Johnson