Reputation: 41
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
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