Skip to main content
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

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.

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.
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
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) , )