Dang-it. So I solved the mystery of grabbing just the data I want. I'm now running into a new issue. I am getting an error when parsing through the file.
Invalid list index 9. In function ListGetAt(list, index [, delimiters]), the value of index, 9, is not a valid as the first argument (this list has 6 elements). Valid indexes are in the range 1 through the number of elements in the list. The error crops up when the loop encounters a field that has null data. I am using ListFix, and i thought that this would solve the problem, but it doesn't. I have no control over the csv files, so I need to figure out a way to accurately input these null fields without throwing an error. Any ideas? ~Steve http://goodcf.instantspot.com/blog Steve Good wrote: > Ha! ok, so ignore my question... I was on the right track, I just > forgot to specify my delimiters correctly in the loop tag. > > ~Steve > http://goodcf.instantspot.com/blog > > > > Steve Good wrote: >> Hi Gang, >> >> About a year ago I wrote (with help from this list) a bit of code to >> loop through a csv file and insert the data into my database. I am >> revisiting that code for a new project but there is a twist now. >> Instead of putting all columns into their respective fields in the >> DB, I want to grab only the columns I need from the CSV and ignore >> the rest, then insert those selected columns into their DB fields. I >> thought by playing around with the index in the cfqueryparam I could >> just have it look at the columns I wanted, obviously this isn't the >> case. Below is a sample of what I tried. I now know that I have to >> start the index at 1 and go up incrementally. Anyone have an idea >> how I could accomplish my task? >> >> <!--- save the uploaded file, overwriting any previous files ---> >> <cffile action="upload" destination="#expandPath('.')#" >> filefield="tdFile" nameconflict="overwrite"> >> <!--- read the uploaded and saved file and store it in variable >> fileData ---> >> <cffile action="read" file="#expandpath(cffile.ServerFile)#" >> variable="fileData"> >> <!--- delete the file, no need to leave it hanging around ---> >> <cffile action="delete" file="#expandpath(cffile.ServerFile)#"> >> <!--- set our counter to zero ---> >> <cfset ctr = 0> >> <!--- include the ListFix UDF ---> >> <cfinclude template="../UDF/ListFix.cfm"> >> <!--- loop through the the data that was set as variable fileData >> ---> >> <cfloop index="record" list="#ListFix(fileData)#" >> delimiters="#chr(9)#"> >> <!--- skip the first 5 rows ---> >> <cfset ctr = ctr +5> >> <cfif ctr NEQ 1> >> <!--- insert the data into the database table ---> >> <cfquery name="qryInsert" datasource="#APPLICATION.ds#"> >> INSERT INTO table (field1, field2, field3, field4, field5, >> field6, field7) >> VALUES ( >> <cfqueryparam >> value="#listgetat(listfix(record),2,chr(9))#" >> cfsqltype="cf_sql_varchar">, >> <cfqueryparam >> value="#listgetat(listfix(record),3,chr(9))#" >> cfsqltype="cf_sql_varchar">, >> <cfqueryparam >> value="#createODBCDateTime(listgetat(listfix(record),6,chr(9)))#" >> cfsqltype="cf_sql_date">, >> <cfqueryparam >> value="#listgetat(listfix(record),9,chr(9))#" >> cfsqltype="cf_sql_varchar">, >> <cfqueryparam value="1050" cfsqltype="cf_sql_varchar">, >> <cfqueryparam value="1050" cfsqltype="cf_sql_varchar">, >> <cfqueryparam value="0" cfsqltype="cf_sql_integer"> >> ) >> </cfquery> >> </cfif> >> </cfloop> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299863 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
