Guest User

Untitled

a guest
May 22nd, 2018
76
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.81 KB | None | 0 0
  1. <cfcomponent>
  2.  
  3. <cffunction name="csvToQuery">
  4.  
  5. <cfargument name="data" default="">
  6. <cfargument name="cols" default="">
  7. <cfargument name="delimiter" default=",">
  8.  
  9. <cfscript>
  10.  
  11. // init
  12. csv = structNew();
  13. loc = structNew();
  14. rtn = structNew();
  15.  
  16. csv.newLine = chr(13) & chr(10);
  17. csv.lineCount = 1;
  18.  
  19. rtn.message = "";
  20. rtn.status = true;
  21. rtn.data = "";
  22.  
  23. if ( listlen(data, csv.newLine) LTE 1 )
  24. {
  25. rtn.status = false;
  26. rtn.message = "No Data";
  27.  
  28. return rtn;
  29. }
  30.  
  31. </cfscript>
  32.  
  33. <cfloop list="#data#" index="csv.line" delimiters="#csv.newLine#">
  34.  
  35. <cfscript>
  36. /*
  37. if ( right( csv.line, 1 ) EQ delimiter )
  38. csv.line = mid( csv.line, 1, len(csv.line)-1 );
  39. */
  40. // get the header
  41. if ( csv.lineCount EQ 1 )
  42. {
  43. csv.header = this.csvLineToArray( csv.line, delimiter );
  44.  
  45. for ( loc.i = 1; loc.i LTE arrayLen(csv.header); loc.i = loc.i + 1 ){
  46. csv.header[loc.i] = rereplacenocase( csv.header[loc.i], "[^a-z0-9]", "", "ALL" );
  47. if(isnumeric(left(csv.header[loc.i],1)))
  48. csv.header[loc.i] = "X#csv.header[loc.i]#";
  49. }
  50.  
  51. // make sure requires columns exist
  52. if ( listlen(arguments.cols) NEQ 0 )
  53. {
  54. for ( loc.i = 1; loc.i LTE listlen(arguments.cols); loc.i = loc.i + 1 )
  55. {
  56. if ( NOT listFindNoCase( arrayToList(csv.header), listGetAt(arguments.cols,loc.i) ) )
  57. {
  58. rtn.status = false;
  59. rtn.message = 'Required Columns Not Found. Column "#listGetAt(arguments.cols,loc.i)#" Not Found.';
  60. rtn.data = csv.header;
  61. return rtn;
  62. }
  63. }
  64. }
  65.  
  66. // create a new query with the header
  67. csv.query = queryNew( arrayToList(csv.header) );
  68. }
  69.  
  70. // insert data into the query
  71. else
  72. {
  73.  
  74. csv.lineArr = this.csvLineToArray( csv.line, delimiter );
  75.  
  76. // check to make sure that the line is the same length as the header
  77. if ( arraylen(csv.lineArr) EQ arrayLen(csv.header) )
  78. {
  79. queryAddRow( csv.query );
  80. for ( loc.i = 1; loc.i LTE arrayLen(csv.header); loc.i = loc.i + 1 )
  81. {
  82. querySetCell( csv.query, rereplaceNoCase( csv.header[loc.i], "[^a-z0-9]", "", "ALL" ), csv.lineArr[loc.i] );
  83. }
  84. }
  85. else
  86. {
  87. rtn.message = rtn.message & "Failed to add row #csv.lineCount#<br>";
  88. }
  89. //writeoutput( arrayToList( csv.lineArr ) & "<br>" );
  90. }
  91.  
  92. // increment the counter
  93. csv.lineCount = csv.LineCount + 1;
  94.  
  95. </cfscript>
  96.  
  97. </cfloop>
  98.  
  99. <cfscript>
  100.  
  101. rtn.data = csv.query;
  102. return rtn;
  103.  
  104. </cfscript>
  105.  
  106. </cffunction>
  107.  
  108. <cffunction name="csvLineToArray">
  109. <cfargument name="line" default="">
  110. <cfargument name="delimiter" default=",">
  111. <cfscript>
  112.  
  113. // init
  114. csvLine = structNew();
  115.  
  116. notFound = true;
  117. notFoundCounter = 1;
  118.  
  119. </cfscript>
  120.  
  121. <cfloop condition="#notFound#">
  122.  
  123. <cfif notFoundCounter GTE 21>
  124. <cfbreak>
  125. </cfif>
  126.  
  127. <cfset line = replace(line,'#delimiter##delimiter#','#delimiter#""#delimiter#',"ALL")>
  128.  
  129. <cfset notFoundCounter = notFoundCounter + 1>
  130.  
  131. </cfloop>
  132.  
  133.  
  134. <cfscript>
  135. if ( right(line,1) EQ #delimiter# )
  136. line = line & '""';
  137.  
  138. // init
  139. csvLine.arr = arrayNew(1);
  140. csvLine.openQuotes = false; // says if the cell is in double quotes
  141.  
  142. for ( csvLine.i = 1; csvLine.i LTE listlen(line, delimiter); csvLine.i = csvLine.i + 1 )
  143. {
  144. csvLine.cell = listgetat( line, csvLine.i, delimiter );
  145.  
  146. // is wrapped in quotes
  147. if ( left( csvLine.cell,1 ) EQ '"' AND right( csvLine.cell,1 ) EQ '"' )
  148. {
  149. csvLine.cell = replace( csvLine.cell, '""','"',"ALL" );
  150.  
  151. if ( trim(csvLine.cell) EQ '"' )
  152. {
  153. csvLine.cell = "";
  154. }
  155.  
  156. else if ( len(trim(csvLine.cell)) NEQ 0 )
  157. {
  158. csvLine.cell = right( csvLine.cell, len(csvLine.cell) - 1 );
  159. csvLine.cell = left( csvLine.cell, len(csvLine.cell) - 1 );
  160. }
  161.  
  162. arrayAppend( csvLine.arr, csvLine.cell );
  163. }
  164.  
  165. // is wrapped in quotes
  166. else if ( left( csvLine.cell,1 ) EQ '"' )
  167. {
  168. csvLine.openQuotes = true;
  169.  
  170. // add the cell to the array without the opening quote
  171. arrayAppend( csvLine.arr, right( csvLine.cell, len(csvLine.cell) - 1 ) );
  172. }
  173.  
  174. // no wrapped in quotes
  175. else if ( NOT csvLine.openQuotes )
  176. {
  177. if ( len(trim(csvLine.cell)) EQ 0 )
  178. csvLine.cell = 'n/a';
  179.  
  180. arrayAppend( csvLine.arr, csvLine.cell );
  181. }
  182.  
  183. else
  184. {
  185.  
  186. if ( right( csvLine.cell,1 ) EQ '"' )
  187. {
  188. // close the quoted string
  189. csvLine.openQuotes = false;
  190. csvLine.cell = left( csvLine.cell, len(csvLine.cell) - 1 );
  191. }
  192.  
  193. // build up the correct cell
  194. csvLine.arr[ arrayLen(csvLine.arr) ] = csvLine.arr[ arrayLen(csvLine.arr) ] & ", " & csvLine.cell;
  195.  
  196. }
  197.  
  198. }
  199.  
  200. return csvLine.arr;
  201.  
  202. </cfscript>
  203.  
  204. </cffunction>
  205.  
  206. </cfcomponent>
Add Comment
Please, Sign In to add comment