2 Replies Latest reply on Oct 14, 2016 3:21 PM by Leonardo Rocha

    Text file with complex layout to table using Teiid

    Leonardo Rocha Newbie

      Hi Folks,

       

      I am learning JBoss Data Virtualization and I configured an example that reads a text file with layout like this:

       

      lastName,FirstName,MiddleName,EmpId,Department,AnnualSalary,Title,HomePhone,MgrId,Street,City,State,ZipCode

      Kisselmeyer,Abbiegale,Tikvica ,9000059,G,64000.00,MGMT WannaBe,670-270-7947,9000073,123 State St.,New York,NY,10001

      Glore,Diodie,Vojvoda ,9000060,G,71000,Associate,480-650-9750,9000073,127 State St.,Los Angeles,CA,10005

      Dawson,Pinckney,Ostoja ,9000061,G,71000,Associate,110-400-3600,9000073,135 State St.,Detroit,MI,10013

       

      In this layout every single line is an independent record and goes to only one table in the Virtual DataBase.

       

      I would like to know if is possible read a file where I have some lines that depend from a previous line, like this:

       

      |0150|2-S0010431506|OSCAR|01058||57394202615||1400050||R  CASTELO BRANCO|123||CENTRO|

      |0200|1-S000012797|AP GSM MOTOROLA W375 PRETO NACIONAL||-|PC|00|85179099||85|||

      |0200|1-S000012845|AP GSM MOTOROLA U3 CINZA-NAC||-|PC|00|85179099||85|||

      |0150|2-SC9290|MICHEL|01058|40432544024321||042229880|1302603||AV AUTAZ MIRIM|1030|Bloco 01,|DISTRITO INDUSTRIAL|

      |0200|1-S000012897|AP GSM PHONE PTO||-|PC|00|85179099||85|||

      |0200|1-S000012979|AP GSM PHONE GRAFITE||-|PC|00|85179099||85|||

      |0200|1-S000013077|AP GSM PHONE W380 ROXO||-|PC|00|85171231||85|||

       

      In this layout every line that starts with 0200 belongs to a line that starts with 0150. It´s like a 1 to n relationship.

       

      Is it possible to read this file to one or more tables?

       

      Thanks a lot.

        • 1. Re: Text file with complex layout to table using Teiid
          Steven Hawkins Master

          In both case you need to use the TEXTTABLE construct, which is like a table valued function - TEXTTABLE · Teiid Documentation

           

          > In this layout every single line is an independent record and goes to only one table in the Virtual DataBase.

           

          That is just a comma separated values:

           

          TEXTTABLE(file COLUMNS lastName string,FirstName string,MiddleName string,EmpId string,Department string,AnnualSalary bigdecimal,Title string,HomePhone string,MgrId string,Street string,City string,State string,ZipCode string HEADER)

           

          Teiid Designer should assist you creating an appropriate view.

           

          > In this layout every line that starts with 0200 belongs to a line that starts with 0150. It´s like a 1 to n relationship.

           

          Having a child line reference a parent is not a problem using SELECTOR, unfortunately the format you show won't work well for that as the lines begin and end with the delimiter.  The SELECTOR logic expects the lines to start with just SELECTOR.  Without the leading | you can do something like:

           

          TEXTTABLE(file SELECTOR '0200' COLUMNS type string, id string, name string, parentId string selector '0150' 2 DELIMITER '|')

           

          That parses each child row and references the parent.

           

          If this s a common format, please submit an enhancement request for direct support.  Otherwise a workaround would be to at least strip the leading |.

          1 of 1 people found this helpful
          • 2. Re: Text file with complex layout to table using Teiid
            Leonardo Rocha Newbie

            Thank you so much Steven.

             

            I will study the TEXTTABLE construct and try to solve the problem.

             

            Best regards.