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

Adam Cameron Adam Cameron added
createPrimeNumbe...
6 day(s) ago

Ray Ford Ray Ford added
timeZoneNow
20 day(s) ago

Henry Ho Henry Ho added
queryExecute
30 day(s) ago

Rick Root Rick Root added
deleteDirectory
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