convert a CSV to query in coldfusion

csv.cfc

<cfcomponent>
	
	<cffunction name="csvToQuery">
		
		<cfargument name="data" default="">
		<cfargument name="cols" default="">
		<cfargument name="delimiter" default=",">
		
		<cfscript>
			
			// init
			csv = structNew();
			loc = structNew();
			rtn = structNew();
			
			csv.newLine = chr(13) & chr(10);
			csv.lineCount = 1;
			
			rtn.message = "";
			rtn.status = true;
			rtn.data = "";
			
			if ( listlen(data, csv.newLine) LTE 1 )
			{
				rtn.status = false;
				rtn.message = "No Data";
				
				return rtn;
			}
			
		</cfscript>
		
		<cfloop list="#data#" index="csv.line" delimiters="#csv.newLine#">
			
			<cfscript>
				/*
				if ( right( csv.line, 1 ) EQ delimiter )
					csv.line = mid( csv.line, 1, len(csv.line)-1 );
				*/
				// get the header
				if ( csv.lineCount EQ 1 )
				{
					csv.header = this.csvLineToArray( csv.line, delimiter );
					
					for ( loc.i = 1; loc.i LTE arrayLen(csv.header); loc.i = loc.i + 1 )
						csv.header[loc.i] = rereplacenocase( csv.header[loc.i], "[^a-z0-9]", "", "ALL" );
					
					// make sure requires columns exist
					if ( listlen(arguments.cols) NEQ 0 )
					{
						for ( loc.i = 1; loc.i LTE listlen(arguments.cols); loc.i = loc.i + 1 )
						{
							if ( NOT listFindNoCase( arrayToList(csv.header), listGetAt(arguments.cols,loc.i) ) )
							{
								rtn.status = false;
								rtn.message = 'Required Columns Not Found. Column "#listGetAt(arguments.cols,loc.i)#" Not Found.';
								rtn.data = csv.header;
								return rtn;
							}
						}
					}
					
					// create a new query with the header
					csv.query = queryNew( arrayToList(csv.header) );
				}
				
				// insert data into the query
				else
				{
					
					csv.lineArr = this.csvLineToArray( csv.line, delimiter );

					// check to make sure that the line is the same length as the header
					if ( arraylen(csv.lineArr) EQ arrayLen(csv.header) )
					{
						queryAddRow( csv.query );
						for ( loc.i = 1; loc.i LTE arrayLen(csv.header); loc.i = loc.i + 1 )
						{
							querySetCell( csv.query, rereplaceNoCase( csv.header[loc.i], "[^a-z0-9]", "", "ALL" ), csv.lineArr[loc.i] );
						}
					}
					else
					{
						rtn.message = rtn.message & "Failed to add row #csv.lineCount#<br>";
					}
					//writeoutput( arrayToList( csv.lineArr ) & "<br>" );
				}
				
				// increment the counter
				csv.lineCount = csv.LineCount + 1;
				
			</cfscript>
			
		</cfloop>
		
		<cfscript>
			
			rtn.data = csv.query;
			return rtn;
		
		</cfscript>
		
	</cffunction>
	
	<cffunction name="csvLineToArray">
		<cfargument name="line" default="">
		<cfargument name="delimiter" default=",">
		<cfscript>
			
			// init
			csvLine = structNew();
			
			notFound = true;
			notFoundCounter = 1;
			
		</cfscript>

		<cfloop condition="#notFound#">
			
			<cfif notFoundCounter GTE 21>
				<cfbreak>
			</cfif>
			
			<cfset line = replace(line,'#delimiter##delimiter#','#delimiter#""#delimiter#',"ALL")>
			
			<cfset notFoundCounter = notFoundCounter + 1>
			
		</cfloop>


		<cfscript>
			if ( right(line,1) EQ #delimiter# )
				line = line & '""';

			// init
			csvLine.arr = arrayNew(1);
			csvLine.openQuotes = false; // says if the cell is in double quotes
			
			for ( csvLine.i = 1; csvLine.i LTE listlen(line, delimiter); csvLine.i = csvLine.i + 1 )
			{
				csvLine.cell = listgetat( line, csvLine.i, delimiter );
				
				// is wrapped in quotes
				if ( left( csvLine.cell,1 ) EQ '"' AND right( csvLine.cell,1 ) EQ '"' )
				{
					csvLine.cell = replace( csvLine.cell, '""','"',"ALL" );
					
					if ( trim(csvLine.cell) EQ '"' )
					{
						csvLine.cell = "";	
					}
					
					else if ( len(trim(csvLine.cell)) NEQ 0 )
					{
						csvLine.cell = right( csvLine.cell, len(csvLine.cell) - 1 );
						csvLine.cell = left( csvLine.cell, len(csvLine.cell) - 1 );
					}
					
					arrayAppend( csvLine.arr, csvLine.cell );
				}
				
				// is wrapped in quotes
				else if ( left( csvLine.cell,1 ) EQ '"' )
				{
					csvLine.openQuotes = true;
					
					// add the cell to the array without the opening quote
					arrayAppend( csvLine.arr, right( csvLine.cell, len(csvLine.cell) - 1 ) );
				}
				
				// no wrapped in quotes
				else if ( NOT csvLine.openQuotes )
				{
					if ( len(trim(csvLine.cell)) EQ 0 )
						csvLine.cell = 'n/a';
					
					arrayAppend( csvLine.arr, csvLine.cell );
				}
				
				else
				{
					
					if ( right( csvLine.cell,1 ) EQ '"' )
					{
						// close the quoted string
						csvLine.openQuotes = false;
						csvLine.cell = left( csvLine.cell, len(csvLine.cell) - 1 );
					}
					
					// build up the correct cell
					csvLine.arr[ arrayLen(csvLine.arr) ] = csvLine.arr[ arrayLen(csvLine.arr) ] & ", " & csvLine.cell;
					
				}
				
			}
			
			return csvLine.arr;
			
		</cfscript>
		
	</cffunction>

</cfcomponent>