user1188254
user1188254

Reputation: 41

csvToArray and insert into database not working as intended

Earlier in the week I was asking the community how I can insert the information from a function called csvToArray written by Ben Nadel, however, I ended up utilizing something else which seems to run just as good and or better. So here is the problem now: After using this new function I am able to grab the data from the array, however when I insert it into the Database I get the following error message from coldfusion :"The element at position 16, of dimension 2, of an array object used as part of an expression, cannot be found." Does anyone have any experience with this and know why I am receiving this message? Everything else works as intended. Here is the code in question:

    <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>

Upvotes: 0

Views: 265

Answers (1)

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

You should be able to just use cfspreadsheet to read the CSV file and move it into the DB. While there are commands for most modern databases to directly import a CSV file (per Leigh's comment), if you don't have the correct permissions, then this is your best bet.

<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">

This returns a normal CF query object which you can loop over for your inserts.

Upvotes: 0

Related Questions