csvToArray并插入到数据库中，使其无法正常工作

Question

本周早些时候，我问社区我如何从better编写的一个名为csvToArray的函数中插入信息，然而，我最终利用了其他一些似乎运行良好或更好的功能。所以现在的问题是:在使用了这个新函数之后，我能够从数组中获取数据，但是当我将它插入数据库时，我从coldfusion获得了以下错误消息：“无法找到作为表达式一部分的数组对象的第16位置的元素，即维度2的元素。”有没有人有这方面的经验，并知道我为什么会收到这条信息？其他的一切都如预期的那样运作。下面是所讨论的代码：

    <cfif request_method is "Post" AND isDefined("form.fileUpload") EQ 1>
        <cffile action="read" file="#form.filecontent#" variable="csvfile">
            <cfinvoke component="#application.path.cfc#.Organizations" method="csvToArray" returnvariable="getArraData">
                <cfinvokeargument name="fileContent" value="#csvfile#">
            </cfinvoke>
    <!--- Loop the array starting on the 2 index to remove the header info from the insert --->
    <cfloop from="2" to="#arrayLen(getArraData)#" index="i">
    <cfset indexNum = i > <!--- Start with 2 index then increment the value by 1 on the loop --->
        <cfloop from="2" to="#arrayLen(getArraData[i])#" index="j">
            <cfset dimNum = j > <!--- Start with 2 index then increment the value by 1 on the loop --->
            <!--- After loop ends. nunbers are dynamically placed into position [2][2] and insert the values of the index  deminsion--->
                <cfquery datasource ="#application.dsn.name#" name="BudgetInsert">
                    <!--- Insert Data here --->
                        INSERT INTO sales_budget
                               (
                                nOrganizationID
                               ,nLocationID
                               ,nBudgetTypeID
                               ,nBudgetYear
                               ,month1
                               ,month2
                               ,month3
                               ,month4
                               ,month5
                               ,month6
                               ,month7
                               ,month8
                               ,month9
                               ,month10
                               ,month11
                               ,month12
                               ,nActive
                               ,tCreationDate
                               ,tLastUpdate
                               ,cChangedBy
                            )
                         VALUES
                               (
                                <cfqueryparam cfsqltype="cf_sql_integer" value="#url.OrgID#">
                               ,<cfqueryparam cfsqltype="cf_sql_integer" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_integer" value="2">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#getArraData[indexNum][dimNum]#">
                               ,<cfqueryparam cfsqltype="cf_sql_bit" value="1">
                               ,GetDate()
                               ,GetDate()
                               ,<cfqueryparam cfsqltype="cf_sql_integer" value="#Session.Auth.UserID#">
                            )
                    </cfquery>
<cfset dimNum = j +1 >
            </cfloop>
indexNum = i +1
        </cfloop>
    </cfif>

Answer 1

您应该能够使用cfspreadsheet读取CSV文件并将其移动到DB中。虽然大多数现代数据库都有直接导入CSV文件的命令(根据Leigh的注释)，但如果您没有正确的权限，那么这是您的最佳选择。

<cfspreadsheet  
    action="read"
    src = "filepath"
    columns = "range"
    columnnames = "comma-delimited list"
    excludeHeaderRow = "true | false"
    format = "CSV|HTML"
    headerrow = "row number"
    name = "text"
    query = "query name"
    rows = "range"
    sheet = "number"
    sheetname = "text">

这将返回一个普通的CF查询对象，您可以对插入进行循环。