Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <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" );
- if(isnumeric(left(csv.header[loc.i],1)))
- csv.header[loc.i] = "X#csv.header[loc.i]#";
- }
- // 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>
Add Comment
Please, Sign In to add comment