-
1. Re: Text file with complex layout to table using Teiid
shawkins Oct 9, 2016 10:39 AM (in response to leonardo.rocha)1 of 1 people found this helpfulIn 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 |.
-
2. Re: Text file with complex layout to table using Teiid
leonardo.rocha Oct 14, 2016 3:21 PM (in response to shawkins)Thank you so much Steven.
I will study the TEXTTABLE construct and try to solve the problem.
Best regards.