Replace all commas with ","...unless the comma is already between quotes?

There's a UDF that will do this, but you still have to strip double quotes (I didn't write it):

<cfscript>
/**
 * CSVFormat accepts the name of an existing query and converts it to csv format.
 * Updated version of UDF orig. written by Simon Horwith
 *
 * @param query        The query to format.
 * @param qualifer        A string to qualify the data with.
 * @param columns        The columns ot use. Defaults to all columns.
 * @return A CSV formatted string.
 * @author Jeff Howden (jeff@members.evolt.org)
 * @version 2, December 3, 2001
 */
function CSVFormat(query)
{
  var returnValue = ArrayNew(1);
  var rowValue = '';
  var columns = query.columnlist;
  var qualifier = '';
  var i = 1;
  var j = 1;
  if(ArrayLen(Arguments) GTE 2) qualifier = Arguments[2];
  if(ArrayLen(Arguments) GTE 3 AND Len(Arguments[3])) columns = Arguments[3];
  returnValue[1] = ListQualify(columns, qualifier);
  ArrayResize(returnValue, query.recordcount + 1);
  columns = ListToArray(columns);
  for(i = 1; i LTE query.recordcount; i = i + 1)
  {
    rowValue = ArrayNew(1);
    ArrayResize(rowValue, ArrayLen(columns));
    for(j = 1; j LTE ArrayLen(columns); j = j + 1)
      rowValue[j] = qualifier & query[columns[j]][i] & qualifier;
    returnValue[i + 1] = ArrayToList(rowValue);
  }            
  returnValue = ArrayToList(returnValue, Chr(13));
  return returnValue;
}
</cfscript>

<!--- Kill double quotes --->
<CFSET CSV = REReplace(CSVFormat(CSV, '"'), """""", """", "ALL")>

This is assuming you're sending it a query called CSV.

-Dain

If you are using MS SQL, use DTS to import the file into a table, even a temp table if you have to, it will still be faster than the processing you want to do with CF.

Have you tried OPENQUERY or OPENDATASOURCE and read the CSV? If that still gives you trouble you could do a CAST( x AS SMALLDATETIME) or something....

Let me know if you want to go this way and need more help..

Hi, give this one a try - shld work for sure ...

Since i dont have the recordsets - i am using CFSETS to give a demo of what ive done - notice that i have my CFSETs enclosed in single quotes ['] - so it dosent clash with the existing ["] in ur recordset ...

<CFSET X1 = '"Column1,Column2,Column3"'>
<CFSET X2 = '"aaaaa,bbbbb,ccccc"'>
<CFSET X3 = 'ababa,"baba, baba",cdcdcd'>
<CFSET X4 = '"ffffff,,dddddd"'>
<CFSET X5 = '"Column1","Column2","Column3"'>
<CFSET X6 = '"ssssss","tttttt","uuuuuu"'>
<CFSET X7 = '"vvvvvvvv","wwwwww","xxxxxx"'>
<CFSET X8 = '"yyyyy","zzzzzz","1111111"'>

<CFOUTPUT>
      <CFLOOP INDEX="i" FROM="1" TO="8">            
            <CFSET "X#i#" = ListQualify(Evaluate('X#i#'),'"',',','All')><!--- Qualify all the elements with ["] --->
            #Replace(Evaluate('X#i#'),'""','"','All')#<BR><!--- Replace the [""] quotes with ["] as some already have ["] --->            
      </CFLOOP>
</CFOUTPUT>

let me know ...

K'Rgds
Anand