4 Replies Latest reply on Apr 8, 2008 4:48 PM by scorpionn_king

    Convert Text to Table

    scorpionn_king
      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
        • 1. Re: Convert Text to Table
          Dan Bracuk Level 5
          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
          • 2. Re: Convert Text to Table
            scorpionn_king Level 1
            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) , )



            • 3. Re: Convert Text to Table
              Dan Bracuk Level 5
              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.
              • 4. Re: Convert Text to Table
                scorpionn_king Level 1
                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.