This content has been marked as final. Show 4 replies
Once you read your file, treat the resulting variable as a set of nested lists. The outer list is delimited by chr(13) and the inner one is delimeted by chr(9).
With nested lists, you can do nested loops.
<cfloop list="#outerlist#" delimters="#chr(13)#">
insert into yoru table
(name, status, company)
<cfloop list="#innerlist#" delimters="#chr(9)#" index = "ThisValue">
some if/else logic for the commas
I am using the same procedure you mentioned, but somehow am missing something.. can you pls.help me
here is my query
<cfloop list="#temp_country#" index="TAB_LIST" delimiters="#chr(13)#">
<CFQUERY NAME="INSERT_DATA" datasource="#dsn#">
INSERT INTO ##ddtempC
<CFSET a = 1>
<cfloop list="#TAB_LIST#" index="FIELD_VALUE" delimiters="#chr(9)#" >
<cfqueryparam value="#FIELD_VALUE#" cfsqltype="CF_SQL_VARCHAR" list="Yes" separator="#chr(9)#">
<cfif a LT 3>,</cfif>
<CFSET a = a + 1>
error am getting is
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'.
The error occurred in \\FILER01\nas\hosted\dev\webapps\vPortal4\portals\vsearch\core\www\data\userReports.cfm: line 78
76 : <CFSET a = a + 1>
78 : </cfloop>
79 : )
80 : </CFQUERY>
SQL INSERT INTO #ddtempC (THEATER_CODE, COUNTRY_CODE, COUNTRY_NAME) VALUES ( (param 1) , )
I never thought of setting list = yes for cfqueryparam. It's a neat trick if it works. To properly attempt that method, take away the inner loop and the if/else logic. The trailing comma won't exist.
Please post back if it works. Then I won't have to try it myself to satisfy my curiousity.
I used a Trim on the TAB_LIST (index = #trim(TAB_LIST)# and that solves the whole problem, i still use the inner comma for separating column values.
thanks for your suggestions Dan.