• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Convert Text to Table

Guest
Apr 08, 2008 Apr 08, 2008

Copy link to clipboard

Copied

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
TOPICS
Advanced techniques

Views

319

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 08, 2008 Apr 08, 2008

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Apr 08, 2008 Apr 08, 2008

Copy link to clipboard

Copied

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



Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 08, 2008 Apr 08, 2008

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Apr 08, 2008 Apr 08, 2008

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation