Skip to main content
Inspiring
October 18, 2006
Question

Converting a CSV to a Query

  • October 18, 2006
  • 1 reply
  • 330 views
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>



This topic has been closed for replies.

1 reply

Inspiring
October 18, 2006
Nevermind :-)