Text file with complex layout to table using Teiid

[leonardo.rocha]

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.

[shawkins]

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

[leonardo.rocha]

Thank you so much Steven.

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

Best regards.