Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>See if this will assist you. Please note that I have modified your initial code for demonstration purposes, but have denoted so you should be able to wire back up to test. This can be tricky... but should give you a good starting point.</p> <p>Please note that there are new tools available within Coldfusion for processing CSV files - I wrote my utilities in 2008 for CF 8, but they still are in use today. Compare and contrast what works for you.</p> <p>Hope this helps.</p> <p>=== cfm page</p> <pre><code>&lt;!---import csv utility component (modify for your pathing)---&gt; &lt;cfset utilcsv = CreateObject("component","webroot.jquery.stackoverflow.csvColumnMap.utils_csv_processing_lib")&gt; &lt;!---declare the csv file (modify for your pathing)---&gt; &lt;cfset arrCSV = utilcsv.readinCSV(ExpandPath('./'),'Report-tstFile.csv') /&gt; &lt;!---declare the header row column values---&gt; &lt;cfset headerRow = listToArray(arrCSV[1],',')&gt; &lt;!---declare the column names query---&gt; &lt;cfset q = QueryNew('offer,fname,lname,address,city,state,zip', 'CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR')&gt; &lt;cfset colList = q.columnList&gt; &lt;!---form submission processing---&gt; &lt;cfif isdefined("form.csvmapsubmit")&gt; &lt;cfset collection = ArrayNew(1)&gt; &lt;!---collect the column and column map values : this step could be eliminated by just assigning the the arrays in the next step, however this allows reference for dump and debug---&gt; &lt;cfloop collection="#form#" item="key"&gt; &lt;cfif FIND('BOFIELD',key) &amp;&amp; trim(StructFind(form,key)) neq ""&gt; &lt;cfset fieldid = ReREPLACE(key,"\D","","all")&gt; &lt;cfset valueKey = 'UPFIELD[' &amp; fieldid &amp; ']'&gt; &lt;cfset t = { 'column'=StructFind(form,key),'value'=StructFind(form,valueKey) }&gt; &lt;cfset arrayappend(collection,t)&gt; &lt;/cfif&gt; &lt;/cfloop&gt; &lt;!---collect the column and column map values : this ensures that the table column is in the same position as the mapped column for the sql statement---&gt; &lt;cfset tblColsArr = ArrayNew(1)&gt; &lt;cfset valColsArr = ArrayNew(1)&gt; &lt;cfloop index="i" from="1" to="#ArrayLen(collection)#"&gt; &lt;cfset arrayappend(tblColsArr, collection[i]['column'])&gt; &lt;cfset arrayappend(valColsArr, collection[i]['value'])&gt; &lt;/cfloop&gt; &lt;!---convert the uploaded data into an array of stuctures for iteration---&gt; &lt;cfset uploadData = utilcsv.processToStructArray(arrCSV)&gt; &lt;!---loop uploaded data---&gt; &lt;cfloop index="y" from="1" to="#ArrayLen(uploadData)#"&gt; &lt;!---create sql command for each record instance---&gt; &lt;cfset sqlCmd = "INSERT INTO Clients(" &amp; arraytolist(tblColsArr) &amp; ") Values("&gt; &lt;cfloop index="v" from="1" to="#ArrayLen(valColsArr)#"&gt; &lt;!---loop over the column maps to pull the approriate value for the table column---&gt; &lt;cfif isNumeric(trim(valColsArr[v])) eq true&gt; &lt;cfset sqlCmd &amp;= trim(uploadData[y][valColsArr[v]])&gt; &lt;cfelse&gt; &lt;cfset sqlCmd &amp;= "'" &amp; trim(uploadData[y][valColsArr[v]]) &amp; "'"&gt; &lt;/cfif&gt; &lt;cfset sqlCmd &amp;= (v lt ArrayLen(valColsArr)) ? "," : ")" &gt; &lt;/cfloop&gt; &lt;!---perform insert for record---&gt; &lt;!--- &lt;cfquery name="insert" datasource=""&gt; #REReplace(sqlCmd,"''","'","ALL")# &lt;!---In the event that the quotation marks are not formatted properly for execution---&gt; &lt;/cfquery&gt; ---&gt; &lt;/cfloop&gt; &lt;/cfif&gt; &lt;form class="formContent960" id="csvmap" name="csvmap" method="post"&gt; &lt;table class="form960" cellpadding="5"&gt; &lt;tbody&gt; &lt;cfloop from="1" to="#ArrayLen(headerRow)#" index="t"&gt; &lt;tr&gt; &lt;td&gt; &lt;!--- Variable Headers ---&gt; &lt;cfif ArrayLen(headerRow) GTE 5&gt; &lt;cfoutput&gt; &lt;select name="upfield[#t#]" class="search" id="Header"&gt; &lt;option selected value=""&gt;--- Headers Uploaded ---&lt;/option&gt; &lt;cfloop from="1" to="#ArrayLen(headerRow)#" index="j"&gt;&lt;option value="#headerRow[j]#"&gt;#headerRow[j]#&lt;/option&gt;&lt;/cfloop&gt; &lt;/select&gt; = &lt;/cfoutput&gt; &lt;/cfif&gt; &lt;/td&gt; &lt;td&gt; &lt;!---Column Constants---&gt; &lt;cfoutput&gt; &lt;select name="bofield[#t#]" class="search" id="Column"&gt; &lt;option selected value=""&gt;--- Headers Clients ---&lt;/option&gt; &lt;cfloop list="#colList#" index="li" delimiters=","&gt;&lt;option value="#li#"&gt;#li#&lt;/option&gt;&lt;/cfloop&gt; &lt;/select&gt; &lt;/cfoutput&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/cfloop&gt; &lt;tr&gt; &lt;td&gt;&amp;nbsp;&lt;/td&gt; &lt;td&gt; &lt;cfoutput&gt; &lt;input type="hidden" name="filelength" id="filelength" value="#ArrayLen(headerRow)#"&gt; &lt;/cfoutput&gt; &lt;input type="submit" name="csvmapsubmit" id="csvmapsubmit"&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/tbody&gt; &lt;/table&gt; &lt;/form&gt; </code></pre> <p>== utils_csv_processing_lib.cfc</p> <pre><code>&lt;!---//////////////////////////////////////////////////////////////////////////////// //// CSV File Processing - Read In File ///// //// Return is array with each array item being a row ///// //// 9.22.08 BP ///// //// ///// /////////////////////////////////////////////////////////////////////////////////---&gt; &lt;cffunction name="readinCSV" access="public" returntype="array"&gt; &lt;cfargument name="fileDirectory" type="string" required="yes"&gt; &lt;cfargument name="fileName" type="string" required="yes"&gt; &lt;!---/// 1. read in selected file ///---&gt; &lt;cffile action="read" file="#fileDirectory##fileName#" variable="csvfile"&gt; &lt;!---///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // 2. set csv file to array ***Note; the orginal csv file ListToArray only used the carrige return/line return as delimiters, /// // so each array value/member is a full record in comma delimited format (i.e.: 01, Fullname, Address1, City, etc) //////////---&gt; &lt;cfset csvList2Array = ListToArray(csvfile, "#chr(10)##chr(13)#")&gt; &lt;cfset ret = checkCSVRowLengths(csvList2Array)&gt; &lt;cfreturn ret&gt; &lt;/cffunction&gt; &lt;!---//////////////////////////////////////////////////////////////////////////////// //// Create Structured Array of CSV FILE ///// //// Return is a structured array uing the colmn header as the struct element name // //// 9.22.08 BP ///// //// ///// //// ****UPDATED 1.6.09********** ///// //// Added empty field file processing - takes empty value ///// //// and replaces with "nul" ///// //// ///// /////////////////////////////////////////////////////////////////////////////////---&gt; &lt;cffunction name="processToStructArray" access="public" returntype="array"&gt; &lt;cfargument name="recordFile" type="array" required="yes"&gt; &lt;!---retrieve the placeholder we are setting for strings containing our default list delimiter (",")---&gt; &lt;cfinvoke component="utils_csv_processing_lib" method="SetGlobalDelimiter" returnvariable="glblDelimiter"&gt; &lt;!---/// 1. get length of array (number of records) in csv file ///---&gt; &lt;cfset csvArrayLen = ArrayLen(recordFile)&gt; &lt;!---///////////////////////////////////////// //// EMPTY VALUE Processing // //////////////////////////////////////////---&gt; &lt;!---// a. create array to hold updated file for processing---&gt; &lt;cfset updatedRowsFnlArr = ArrayNew(1)&gt; &lt;!---// b. loop entire csv file to process each row---&gt; &lt;cfloop index="li2" from="1" to="#csvArrayLen#"&gt; &lt;!---// c. grab each column (delimited by ",") for internal loop. *******The value of each array index/item is a comma delimited list*******---&gt; &lt;cfset currRecRow = #recordFile[li2]#&gt; &lt;!---/// d. loop each row in file---&gt; &lt;cfloop list="#currRecRow#" index="updateRowindex" delimiters="#chr(10)##chr(13)#"&gt; &lt;!---// e. find and replace empty column values in list with a set value for processing---&gt; &lt;!---consolidated for single list output per array index: regenerates a value of val,val,val for a value of val,,val---&gt; &lt;!---// process middle positions in list //---&gt; &lt;cfset currRowListed = updateRowindex&gt; &lt;cfset updatedRowListed = REreplace(currRowListed,",,",",nul,","ALL")&gt; &lt;cfset updatedRowListed = REreplace(updatedRowListed,",,",",nul,","ALL")&gt; &lt;!---// process 1st position in list //---&gt; &lt;cfset frstpos = REFIND(",",updatedRowListed,1)&gt; &lt;cfif frstpos EQ 1&gt; &lt;cfset updatedRowListed = REReplace(updatedRowListed,",","nul,","one")&gt; &lt;/cfif&gt; &lt;!---// process last position in list //---&gt; &lt;cfset rowStrngLen = Len(updatedRowListed)&gt; &lt;cfset lastpos = REFIND(",",updatedRowListed,rowStrngLen)&gt; &lt;cfif lastpos EQ rowStrngLen&gt; &lt;cfset updatedRowListed = updatedRowListed &amp; "nul"&gt; &lt;/cfif&gt; &lt;!---// f. append current row with updated value of 'nul' for empty list positions to array---&gt; &lt;cfset ArrayAppend(updatedRowsFnlArr, updatedRowListed)&gt; &lt;/cfloop&gt; &lt;/cfloop&gt; &lt;!---/// 2. get number of records in updated array---&gt; &lt;cfset updatedRowsFnlLen = ArrayLen(updatedRowsFnlArr)&gt; &lt;!---/// 3. set the first item in the array to a variable (at postion 1). This will set the entire first record to the variable, delimited by commas ///---&gt; &lt;cfset getRecColumns = updatedRowsFnlArr[1]&gt; &lt;!---/// 4. get length of 1st record row, which will tell us hom many columns are in the csv file ///---&gt; &lt;cfset ColumnCount = ListLen(updatedRowsFnlArr[1],",")&gt; &lt;!---/// 5. create array to hold value for return and start loop of list *****Loop started at 2 to exclude header row***** ///---&gt; &lt;cfset recordArr = ArrayNew(1)&gt; &lt;cfloop index="i" from="2" to="#updatedRowsFnlLen#"&gt; &lt;!---/// 6. grab each column (delimited by ",") internal loop. The value of each array index/item is a comma delimited list ///---&gt; &lt;cfset currRecRow = #updatedRowsFnlArr[i]#&gt; &lt;!---/// 7. We now create a structure and assign each row value to the corresponding header within the structure ///---&gt; &lt;cfset recordStruct = StructNew()&gt; &lt;cfloop index="internal" from="1" to="#ColumnCount#"&gt; &lt;!---conditional to set the 'nul' value added for empty list position values in order to process back to empty values---&gt; &lt;cfif listGetAt(currRecRow,internal,",") NEQ 'nul'&gt; &lt;!---check for global placeholder delimiter and reset to ","---&gt; &lt;cfif FIND(glblDelimiter,listGetAt(currRecRow,internal,",")) NEQ 0&gt; &lt;cfset resetDelimiterVal = Replace(listGetAt(currRecRow,internal,","),glblDelimiter,',','All')&gt; &lt;cfelse&gt; &lt;cfset resetDelimiterVal = listGetAt(currRecRow,internal,",")&gt; &lt;/cfif&gt; &lt;cfset recordStruct[listGetAt(getRecColumns,internal,",")] = resetDelimiterVal&gt; &lt;cfelse&gt; &lt;cfset recordStruct[listGetAt(getRecColumns,internal,",")] = ""&gt; &lt;/cfif&gt; &lt;/cfloop&gt; &lt;!---/// 8. append the struct to the array ///---&gt; &lt;cfset ArrayAppend(recordArr,recordStruct)&gt; &lt;/cfloop&gt; &lt;cfreturn recordArr&gt; &lt;/cffunction&gt; &lt;!---//////////////////////////////////////////////////////////////////////////////// //// SetGlobalDelimiter ///// //// Sets a placeholder for strings containing the primary delimiter (",") ///// //// 02.6.11 BP ///// /////////////////////////////////////////////////////////////////////////////////---&gt; &lt;cffunction name="SetGlobalDelimiter" access="public" returntype="string" hint="set a placeholder delimiter for the strings that contain the primary list comma delimiter"&gt; &lt;cfset glblDelimiter = "{_$_}"&gt; &lt;cfreturn glblDelimiter&gt; &lt;/cffunction&gt; </code></pre> <p>===missing cfc function</p> <pre><code>&lt;!---//////////////////////////////////////////////////////////////////////////////////////////////////////// //// checkCSVRowLengths ///// //// due to some inconsistencies in excel, some csv files drop the delimiter if list is empty ///// //// 7.20.11 BP ///// /////////////////////////////////////////////////////////////////////////////////////////////////////////---&gt; &lt;cffunction name="checkCSVRowLengths" access="public" returntype="array"&gt; &lt;cfargument name="readArray" type="array" required="yes"&gt; &lt;cfset column_row = readArray[1]&gt; &lt;cfset column_row_len = listlen(column_row,',')&gt; &lt;cfloop index="i" from="2" to="#ArrayLen(readArray)#"&gt; &lt;cfset updateRowindex = readArray[i]&gt; &lt;cfif listlen(updateRowindex) lt column_row_len&gt; &lt;!---// process middle positions in list //---&gt; &lt;cfset currRowListed = updateRowindex&gt; &lt;cfset updatedRowListed = REreplace(currRowListed,",,",",nul,","ALL")&gt; &lt;cfset updatedRowListed = REreplace(updatedRowListed,",,",",nul,","ALL")&gt; &lt;!---// process 1st position in list //---&gt; &lt;cfset frstpos = REFIND(",",updatedRowListed,1)&gt; &lt;cfif frstpos EQ 1&gt; &lt;cfset updatedRowListed = REReplace(updatedRowListed,",","nul,")&gt; &lt;/cfif&gt; &lt;!---// process last position in list //---&gt; &lt;cfset rowStrngLen = Len(updatedRowListed)&gt; &lt;cfset lastpos = REFIND(",",updatedRowListed,rowStrngLen)&gt; &lt;cfif lastpos EQ rowStrngLen&gt; &lt;cfset updatedRowListed = updatedRowListed &amp; "nul"&gt; &lt;/cfif&gt; &lt;cfelse&gt; &lt;cfset updatedRowListed = updateRowindex&gt; &lt;/cfif&gt; &lt;cfif listlen(updatedRowListed) lt column_row_len&gt; &lt;cfset lc = column_row_len - listlen(updatedRowListed)&gt; &lt;cfloop index="x" from="1" to="#lc#"&gt; &lt;cfset updatedRowListed = updatedRowListed &amp; ',nul'&gt; &lt;/cfloop&gt; &lt;/cfif&gt; &lt;cfset readArray[i] = updatedRowListed&gt; &lt;/cfloop&gt; &lt;cfreturn readArray&gt; &lt;/cffunction&gt; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POLoop through 2 slectboxes submitted values to map to an update query. Coldfusion
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USbphillips
    1. USbphillips
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThank you, that's very impressive. I have already begun working on this. I will keep you posted. Thanks.
      singulars
      1. PO
      1. USSteve Ontologic
    2. COPlease do. Hope it helps!
      singulars
      1. PO
      1. USbphillips
    3. COI'm having trouble with the CFC. ERROR: Variable CHECKCSVROWLENGTHS is undefined. Line 17: <cfset ret = checkCSVRowLengths(csvList2Array)>
      singulars
      1. PO
      1. USSteve Ontologic
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload