Question
Converting a CSV to a Query
My site used to work using cfhttp to find and convert and
uploaded csv file,
but an upgrade to Server 2003 killed it. I found the 'fix' for the
compression problem with 2003, but it didn't work for me.
I found a UDF that converts a csv to a query, but I don't know how to
impliment it. I want to grab a csv from a folder and convert it. The example
shows a csv being created using <cfsavecontent>. Can it be used with a file?
If so, how can I point it to the csv file.
Example
<cfsavecontent variable="newCSV">col1,col2,col3
row1val1,row1val2
row2val1,row2val2,row2val3
</cfsavecontent>
<cfdump var="#CSVToQuery(newCSV)#">
Here is the UDF:
<cfscript>
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 , 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,listGetAt(listGetAt(csvString,i,rowDelim),j,colDelim),
i-1);
}
}
}
return newQuery;
}
</cfscript>
but an upgrade to Server 2003 killed it. I found the 'fix' for the
compression problem with 2003, but it didn't work for me.
I found a UDF that converts a csv to a query, but I don't know how to
impliment it. I want to grab a csv from a folder and convert it. The example
shows a csv being created using <cfsavecontent>. Can it be used with a file?
If so, how can I point it to the csv file.
Example
<cfsavecontent variable="newCSV">col1,col2,col3
row1val1,row1val2
row2val1,row2val2,row2val3
</cfsavecontent>
<cfdump var="#CSVToQuery(newCSV)#">
Here is the UDF:
<cfscript>
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 , 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
}
}
}
return newQuery;
}
</cfscript>