Ok, I'm attacking this thing from another angle. I am trying to use cfhttp to read the file, however, I need to skip the first 5 lines of the CSV. The original document has a bunch of garbage at the top that I don't want. How do I skip the lines while using cfhttp? Or do I need to define the column names and just hope for the best?
Thanks! ~Steve http://goodcf.instantspot.com/blog Steve Good wrote: > 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:299868 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
