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>
--
~Steve
http://goodcf.instantspot.com/blog
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:299846
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
