Query to CSV

query-to-csv.cfm

<cfscript>
function downloadFile( required string filePath ){
	var file_separator = '/';
	var mimeType=getMimeType(arguments.filePath);
	if(findNoCase("windows",server.os.name)){
		file_separator = '\';
	}

	cfheader(  name="Content-disposition" value="attachment;filename=#scrubFileName(listLast(filePath,file_separator))#" );
	cfcontent( type="#mimeType#" file="#arguments.filePath#" deleteFile="true" reset="true" );
	flush;
	abort;
}

public string function QueryToCSV( q, name="DataFile", sort=false ){
  var tmp_directory = expandPath( "/includes/tmp" );
  if(!directoryExists( tmp_directory ) directoryCreate( tmp_directory );
	var filePath = this.tmp_directory&arguments.name&".csv";
	var columnList = getColumnListArray(arguments.q,arguments.sort);
	var d = chr(10);
	var content = arrayToList(columnList)&d;
	for(row in arguments.q){
		var vals = arrayNew(1);
		var i = 1;
		//create an array from our row structure and santize the data
		for(i=1; i LTE arrayLen(columnList);i=i+1){
			 	arrayAppend(vals,replace(row[columnList[i]],',','&##44;','all'));			
		} 
		content &= arrayToList(vals)&d;
	}
	//write the file
	fileWrite( filePath, content );
	return filePath;
}	

private array function getColumnListArray(q,sort=false){
	var columnList=q.getColumnNames();

	if(arguments.sort){
		//convert and reconvert our array to make it sortable
		columnList=arrayToList(columnList);
		columnList=listToArray(columnList);
		//now sort our list and return it
		arraySort(columnList,'textnocase','asc');
	}

	return columnList;	
}

private string function scrubFileName(required string fileName){
    var extension = reverse(listfirst(reverse(arguments.fileName),"."));
    arguments.fileName = reverse(listrest(reverse(arguments.fileName),"."));
    arguments.fileName = Replace(arguments.fileName, ' ', '_', 'all');
    arguments.fileName = REReplace(arguments.fileName, '\W', '', 'all');
    return arguments.fileName & "." & extension;
}
</cfscript>