Skip to main content
A
Anonymous
April 8, 2008
Question

Convert Text to Table

  • April 8, 2008
  • 3 replies
  • 368 views
I am trying to Convert Text (tab delimited text file) to insert records into a table. but not sure how to do this, can anybody throw some light on this

here is the tab delimited text file.

JOHN WORKING MY COMPANY
KRIS MECHANIC OTHER COMPANY
JAY WORKER THIS COMPANY
SADD EMPLOYEE THAT COMPANY


i want to insert them exactly as rows in a table which has columns ( NAME, STATUS, COMPANY )

Thanks in Advance..for helping
This topic has been closed for replies.

3 replies

A
Anonymous
April 8, 2008
Hi Dan,

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.

D
Dan_Bracuk
Inspiring
April 8, 2008
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.
D
Dan_Bracuk
Inspiring
April 8, 2008
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)
values
(
<cfloop list="#innerlist#" delimters="#chr(9)#" index = "ThisValue">
<cfqueryparam value="#thisvalue"#>
some if/else logic for the commas
)
closing tags
A
Anonymous
April 8, 2008
Hi

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
(THEATER_CODE,
COUNTRY_CODE,
COUNTRY_NAME)
VALUES (

<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>

</cfloop>
)
</CFQUERY>
</cfloop>


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>
77 :
78 : </cfloop>
79 : )
80 : </CFQUERY>

SQL INSERT INTO #ddtempC (THEATER_CODE, COUNTRY_CODE, COUNTRY_NAME) VALUES ( (param 1) , )


Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices