Прокрутите 2 представленных значения селектбоксов, чтобы сопоставить их с запросом на обновление. Холодный синтез

Альтернативный подход

Прежде чем я перейду к вашей текущей форме, позвольте мне упомянуть еще один вариант: использование инструментов импорта вашей базы данных, таких как OPENROWSET или МАССОВАЯ ВСТАВКА. Первый немного более гибкий, его можно использовать из оператора SELECT. Таким образом, вы можете сделать прямую вставку из файла CSV, без зацикливания. (Обычно я предпочитаю сначала вставлять во временную таблицу. Запустите несколько запросов проверки, затем insert/select данные в основную таблицу. Но это зависит от приложения..)

В любом случае, как только вы проверили имена столбцов, вставка с OPENROWSET — это всего лишь один запрос:

<!--- see below for how to validate list of column names --->
<cfquery name="insertRawData" datasource="yourDSN">
   INSERT INTO YourTable ( #theSelectedColumnNames# )
   SELECT  * 
   FROM    OPENROWSET( 'Microsoft.Jet.OLEDB.4.0'
            ,'text;HDR=YES;Database=c:\some\path\'
            , 'SELECT * FROM [yourFileName.csv]'  )
</cfquery>

Текущий подход

Форма:

Используя ваш текущий метод, вам нужно будет дважды прочитать файл CSV: один раз на странице «сопоставление» и снова на странице действий. Технически это может быть так же просто, как дать списку выбора столбца БД одно и то же имя. Таким образом, имена будут представлены в виде списка с разделителями-запятыми:

<cfset csvHeaders = csvData[1]>
<cfloop array="#csvHeaders#" index="headerName">
    <cfoutput>
        Map file header: #headerName# 
        to column:  
        <select name="targetColumns">
            <option value="" selected>--- column name---</option>
            <cfloop query="getColumnNames">
                <option value="#column_name#">#column_name#</option>
            </cfloop>
        </select>
    </cfoutput>
    <br>
</cfloop>

Проверить столбцы:

Затем повторно проверьте список имен столбцов в соответствии с вашими метаданными базы данных, чтобы предотвратить внедрение sql. Не пропускайте этот шаг!. (Вместо этого вы также можете использовать отдельную таблицу сопоставления, чтобы не раскрывать схему базы данных. Это мое предпочтение.)

<cfquery name="qVerify" datasource="yourDSN">
    SELECT COUNT(COLUMN_NAME) AS NumberOfColumns    
    FROM   INFORMATION_SCHEMA.COLUMNS
    WHERE  TABLE_NAME = 'YourTableName'
    AND    COLUMN_NAME IN 
           (
        <cfqueryparam value="#form.targetColumns#" cfsqltype="cf_sql_varchar">
           )
</cfquery>

<cfif qVerify.recordCount eq 0 OR qVerify.NumberOfColumns neq listLen(form.targetColumns)>
    ERROR. Missing or invalid column name(s) detected
    <cfabort>
</cfif>

Вставить данные:

Наконец, перечитайте файл CSV и выполните цикл, чтобы вставить каждую строку. Ваш фактический код должен содержать НАМНОГО больше проверки (обработка недопустимых имен столбцов и т. д.), но это основная идея:

<cfset csvData  = CSVToArray(....)>
<!--- deduct one to skip header row --->
<cfset numberOfRows = arrayLen(csvData) - 1>    
<cfset numberOfColumns = arrayLen(csvData[1])>  
<cfif numberOfColumns eq 0 OR numberOfColumns neq listLen(form.targetColumns)>
    ERROR. Missing or invalid column name(s) detected
    <cfabort>
</cfif>

<cfloop from="1" to="#numberOfRows#" index="rowIndex">
    <cfquery ...>
        INSERT INTO ClientColumnMappings ( #form.targetColumns# )
        VALUES 
        (
            <cfloop from="1" to="#numberOfColumns#" index="colIndex">
                 <cfif colIndex gt 1>,</cfif>
                 <cfqueryparam value="#csvData[rowIndex][colIndex]#" cfsqltype="cf_sql_varchar">
            </cfloop>
        )
    </cfquery>
</cfloop>

Посмотрите, поможет ли это вам. Обратите внимание, что я изменил ваш первоначальный код в демонстрационных целях, но обозначил, что вы сможете выполнить резервное копирование для тестирования. Это может быть сложно... но должно дать вам хорошую отправную точку.

Обратите внимание, что в Coldfusion доступны новые инструменты для обработки CSV-файлов — я написал свои утилиты в 2008 году для CF 8, но они все еще используются сегодня. Сравните и сопоставьте, что работает для вас.

Надеюсь это поможет.

=== cfm страница

<!---import csv utility component (modify for your pathing)--->
<cfset utilcsv = CreateObject("component","webroot.jquery.stackoverflow.csvColumnMap.utils_csv_processing_lib")>
<!---declare the csv file (modify for your pathing)--->
<cfset arrCSV = utilcsv.readinCSV(ExpandPath('./'),'Report-tstFile.csv') />
<!---declare the header row column values--->
<cfset headerRow = listToArray(arrCSV[1],',')>
<!---declare the column names query--->
<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')>
<cfset colList = q.columnList>  

<!---form submission processing--->
<cfif isdefined("form.csvmapsubmit")>

    <cfset collection = ArrayNew(1)>
    <!---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--->
    <cfloop collection="#form#" item="key">
        <cfif FIND('BOFIELD',key) && trim(StructFind(form,key)) neq "">
            <cfset fieldid = ReREPLACE(key,"\D","","all")>
            <cfset valueKey = 'UPFIELD[' & fieldid & ']'>
            <cfset t = { 'column'=StructFind(form,key),'value'=StructFind(form,valueKey) }>
            <cfset arrayappend(collection,t)>
        </cfif>
    </cfloop>

    <!---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--->
    <cfset tblColsArr = ArrayNew(1)>
    <cfset valColsArr = ArrayNew(1)>
    <cfloop index="i" from="1" to="#ArrayLen(collection)#">
        <cfset arrayappend(tblColsArr, collection[i]['column'])>
        <cfset arrayappend(valColsArr, collection[i]['value'])>
    </cfloop>

    <!---convert the uploaded data into an array of stuctures for iteration--->
    <cfset uploadData = utilcsv.processToStructArray(arrCSV)>

    <!---loop uploaded data--->
    <cfloop index="y" from="1" to="#ArrayLen(uploadData)#">

        <!---create sql command for each record instance--->
        <cfset sqlCmd = "INSERT INTO Clients(" & arraytolist(tblColsArr) & ") Values(">
        <cfloop index="v" from="1" to="#ArrayLen(valColsArr)#">
            <!---loop over the column maps to pull the approriate value for the table column--->
            <cfif isNumeric(trim(valColsArr[v])) eq true>
                <cfset sqlCmd &= trim(uploadData[y][valColsArr[v]])>
             <cfelse>
                <cfset sqlCmd &= "'" & trim(uploadData[y][valColsArr[v]]) & "'">
             </cfif>
             <cfset sqlCmd &=  (v lt ArrayLen(valColsArr)) ? "," : ")" >
        </cfloop>

        <!---perform insert for record--->
        <!--- 
        <cfquery name="insert" datasource="">
        #REReplace(sqlCmd,"''","'","ALL")# <!---In the event that the quotation marks are not formatted properly for execution--->
        </cfquery>
        --->
    </cfloop>

</cfif>

<form class="formContent960" id="csvmap" name="csvmap" method="post">
<table class="form960" cellpadding="5">
    <tbody>
    <cfloop from="1" to="#ArrayLen(headerRow)#" index="t">
    <tr>
        <td>
        <!--- Variable Headers --->
        <cfif ArrayLen(headerRow) GTE 5>
            <cfoutput>
            <select name="upfield[#t#]" class="search" id="Header">
                <option selected value="">--- Headers Uploaded ---</option>
                <cfloop from="1" to="#ArrayLen(headerRow)#" index="j"><option value="#headerRow[j]#">#headerRow[j]#</option></cfloop>
            </select> =
            </cfoutput>                
        </cfif>
        </td>
        <td>
        <!---Column Constants--->
        <cfoutput>
        <select name="bofield[#t#]" class="search" id="Column">            
            <option selected value="">--- Headers Clients ---</option>
            <cfloop list="#colList#" index="li" delimiters=","><option value="#li#">#li#</option></cfloop>
        </select>
        </cfoutput>
        </td>
    </tr>
    </cfloop>
    <tr>
    <td>&nbsp;</td>
    <td>
    <cfoutput>
    <input type="hidden" name="filelength" id="filelength" value="#ArrayLen(headerRow)#">
    </cfoutput>
    <input type="submit" name="csvmapsubmit" id="csvmapsubmit">
    </td>
    </tr>
    </tbody>
</table>
</form>

== utils_csv_processing_lib.cfc

<!---////////////////////////////////////////////////////////////////////////////////
////                    CSV File Processing - Read In File                      /////
////                Return is array with each array item being a row            /////
////                            9.22.08 BP                                      /////
////                                                                            /////
/////////////////////////////////////////////////////////////////////////////////---> 
    <cffunction name="readinCSV" access="public" returntype="array">
        <cfargument name="fileDirectory" type="string" required="yes">
        <cfargument name="fileName" type="string" required="yes">  
        <!---/// 1. read in selected file ///--->
        <cffile action="read" file="#fileDirectory##fileName#" variable="csvfile">
    <!---/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//  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) //////////--->    
        <cfset csvList2Array = ListToArray(csvfile, "#chr(10)##chr(13)#")> 

        <cfset ret = checkCSVRowLengths(csvList2Array)>
        <cfreturn ret>
    </cffunction>

<!---////////////////////////////////////////////////////////////////////////////////
////                    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"                             /////
////                                                                            /////
/////////////////////////////////////////////////////////////////////////////////---> 
    <cffunction name="processToStructArray" access="public" returntype="array">
        <cfargument name="recordFile" type="array" required="yes">


     <!---retrieve the placeholder we are setting for strings containing our default list delimiter (",")--->
        <cfinvoke component="utils_csv_processing_lib" method="SetGlobalDelimiter" returnvariable="glblDelimiter">

    <!---/// 1. get length of array (number of records) in csv file ///--->
        <cfset csvArrayLen = ArrayLen(recordFile)>

        <!---/////////////////////////////////////////
        ////        EMPTY VALUE Processing          //
        //////////////////////////////////////////--->
                <!---// a. create array to hold updated file for processing--->
                    <cfset updatedRowsFnlArr = ArrayNew(1)>

                <!---// b. loop entire csv file to process each row--->
                    <cfloop index="li2" from="1" to="#csvArrayLen#">

                    <!---// c. grab each column (delimited by ",") for internal loop. *******The value of each array index/item is a comma delimited list*******--->
                    <cfset currRecRow = #recordFile[li2]#>

                    <!---/// d. loop each row in file--->
                    <cfloop list="#currRecRow#" index="updateRowindex" delimiters="#chr(10)##chr(13)#">
                          <!---// e. find and replace empty column values in list with a set value for processing--->
                          <!---consolidated for single list output per array index: regenerates a value of val,val,val for a value of val,,val--->

                          <!---// process middle positions in list //--->
                          <cfset currRowListed = updateRowindex>
                          <cfset updatedRowListed = REreplace(currRowListed,",,",",nul,","ALL")>
                          <cfset updatedRowListed = REreplace(updatedRowListed,",,",",nul,","ALL")>
                          <!---// process 1st position in list //--->
                          <cfset frstpos = REFIND(",",updatedRowListed,1)>
                          <cfif frstpos EQ 1>
                          <cfset updatedRowListed = REReplace(updatedRowListed,",","nul,","one")>
                          </cfif>
                          <!---// process last position in list //--->
                          <cfset rowStrngLen = Len(updatedRowListed)>
                          <cfset lastpos = REFIND(",",updatedRowListed,rowStrngLen)>
                          <cfif lastpos EQ rowStrngLen>
                          <cfset updatedRowListed = updatedRowListed & "nul">
                          </cfif>

                          <!---// f. append current row with updated value of 'nul' for empty list positions to array--->
                          <cfset ArrayAppend(updatedRowsFnlArr, updatedRowListed)>
                     </cfloop>
                </cfloop>

        <!---/// 2. get number of records in updated array--->
        <cfset updatedRowsFnlLen = ArrayLen(updatedRowsFnlArr)>

        <!---/// 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 ///--->
        <cfset getRecColumns = updatedRowsFnlArr[1]>

        <!---/// 4. get length of 1st record row, which will tell us hom many columns are in the csv file ///--->
        <cfset ColumnCount = ListLen(updatedRowsFnlArr[1],",")>

        <!---/// 5. create array to hold value for return and start loop of list *****Loop started at 2 to exclude header row***** ///--->
        <cfset recordArr = ArrayNew(1)>
        <cfloop index="i" from="2" to="#updatedRowsFnlLen#">

        <!---/// 6. grab each column (delimited by ",") internal loop. The value of each array index/item is a comma delimited list ///--->
        <cfset currRecRow = #updatedRowsFnlArr[i]#>

        <!---/// 7. We now create a structure and assign each row value to the corresponding header within the structure ///--->
        <cfset recordStruct = StructNew()>
        <cfloop index="internal" from="1" to="#ColumnCount#">
            <!---conditional to set the 'nul' value added for empty list position values in order to process back to empty values--->
            <cfif listGetAt(currRecRow,internal,",") NEQ 'nul'>

                    <!---check for global placeholder delimiter and reset to ","--->
                    <cfif FIND(glblDelimiter,listGetAt(currRecRow,internal,",")) NEQ 0>
                        <cfset resetDelimiterVal = Replace(listGetAt(currRecRow,internal,","),glblDelimiter,',','All')>
                    <cfelse>
                        <cfset resetDelimiterVal = listGetAt(currRecRow,internal,",")>
                    </cfif>

                <cfset recordStruct[listGetAt(getRecColumns,internal,",")] = resetDelimiterVal>
            <cfelse>
                <cfset recordStruct[listGetAt(getRecColumns,internal,",")] = "">
            </cfif>
        </cfloop>
        <!---/// 8. append the struct to the array ///--->
        <cfset ArrayAppend(recordArr,recordStruct)>
        </cfloop>
        <cfreturn recordArr>
    </cffunction>

<!---////////////////////////////////////////////////////////////////////////////////
////                              SetGlobalDelimiter                            /////
////    Sets a placeholder for strings containing the primary delimiter (",")   /////
////                                    02.6.11 BP                              /////
/////////////////////////////////////////////////////////////////////////////////--->     
    <cffunction name="SetGlobalDelimiter" access="public" returntype="string" hint="set a placeholder delimiter for the strings that contain the primary list comma delimiter">
        <cfset glblDelimiter = "{_$_}">
      <cfreturn glblDelimiter>
    </cffunction>  

===отсутствует функция cfc

<!---////////////////////////////////////////////////////////////////////////////////////////////////////////
////                        checkCSVRowLengths                                                          /////
////  due to some inconsistencies in excel, some csv files drop the delimiter if list is empty          /////
////                            7.20.11 BP                                                              /////
/////////////////////////////////////////////////////////////////////////////////////////////////////////---> 
<cffunction name="checkCSVRowLengths" access="public" returntype="array">
        <cfargument name="readArray" type="array" required="yes">

 <cfset column_row = readArray[1]>
       <cfset column_row_len = listlen(column_row,',')>

       <cfloop index="i" from="2" to="#ArrayLen(readArray)#">
            <cfset updateRowindex = readArray[i]>


            <cfif listlen(updateRowindex) lt column_row_len>

                         <!---// process middle positions in list //--->
                          <cfset currRowListed = updateRowindex>
                          <cfset updatedRowListed = REreplace(currRowListed,",,",",nul,","ALL")>
                          <cfset updatedRowListed = REreplace(updatedRowListed,",,",",nul,","ALL")>
                          <!---// process 1st position in list //--->
                          <cfset frstpos = REFIND(",",updatedRowListed,1)>
                          <cfif frstpos EQ 1>
                          <cfset updatedRowListed = REReplace(updatedRowListed,",","nul,")>
                          </cfif>
                          <!---// process last position in list //--->
                          <cfset rowStrngLen = Len(updatedRowListed)>
                          <cfset lastpos = REFIND(",",updatedRowListed,rowStrngLen)>
                          <cfif lastpos EQ rowStrngLen>
                          <cfset updatedRowListed = updatedRowListed & "nul">
                          </cfif>
             <cfelse>

                <cfset  updatedRowListed  = updateRowindex>    

            </cfif>

            <cfif listlen(updatedRowListed) lt column_row_len>

                <cfset lc = column_row_len - listlen(updatedRowListed)>

                <cfloop index="x" from="1" to="#lc#">
                    <cfset updatedRowListed = updatedRowListed & ',nul'>
                </cfloop>


            </cfif>


            <cfset readArray[i] = updatedRowListed>
       </cfloop>



        <cfreturn readArray>
    </cffunction>