1 Reply Latest reply on Oct 18, 2006 8:56 AM by Newsgroup_User

    Converting a CSV to a Query

    Level 7
      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[j],listGetAt(listGetAt(csvString,i,rowDelim),j,colDelim), i-1);
      }
      }
      }
      return newQuery;
      }
      </cfscript>