topic Re: [resolved] [tFileInputDelimited] - Extract header and input as column var in rows in Talend Studio

[resolved] [tFileInputDelimited] - Extract header and input as column var in rows

Anonymous — Sat, 16 Nov 2024 11:31:12 GMT

Hello,
I'm beginning with Talend for Data Integration, and I've been struggling to build the below process,
any help would be much appreciated.
My goals is the following:
- read a directory containing csv files and input in tFileInputDelimited => ok
- extract header (2 lines) then memorize the value of the row1 columns in order to reuse them
- Then build a csv outfile with the following output : <Value of the header (row1)>, <value of column1 of each row > 2 >, <value of of column2 of each row > 2> ....
Basically I want the value of the header (row1) as a constant in the column1 of each row > 2.
I have been trying different solution one with a tmap to split the out in 2 rows based on a regex on first row, but when I'm trying to merge those into the output row afterwards with a tjavarow, value is appearing only on the first line because of the iteration process.
I also tried with tSetGlobalVar but I didn't find a way to input row value into a GlobalVar.
Also tried with tMemorize row and calling the memorized row in the tJavaRow (see screenshot below) but no luck again because of the iteration process I guess, I'm having only the first row in the output file filled with the value of row1
If you have any idea on how I could achieve this with Talend it would be nice, if not I'll do this task by standard scripting.
Many thanks in advance
Regards
Flo

Re: [resolved] [tFileInputDelimited] - Extract header and input as column var in rows

rbaldwin — Tue, 02 Sep 2014 19:36:24 GMT

This is somewhat straightforward to do:
Off the tFileList link it to a tFileInputDelimited configured to have a limit of 1 row and connect this to a tSetGlobalVar and put your row into it (i.e. row3 as the value)
Next connect it to another tFileInputDelimited with Headers configured at 2 and limit is empty.
Connect the second tFileInputDelimited to a tMap and put all of your columns in an output on it. For the columns that you're pulling in from the header row use ((<row you cached>Struct)globalMap.get("my value")).<coloumn(s)> to get the cached row into the file.
I've attached a working example that does this - it'll also generate the file that it will read if that portion is activated again. Edit to add: the example was made in TOS_DI 5.5.
ExampleJob.zip.zip
Hope that helps.

Re: [resolved] [tFileInputDelimited] - Extract header and input as column var in rows

Anonymous — Tue, 02 Sep 2014 22:13:11 GMT

Hi,
It actually help a lot, unfortunately was not able to open you file with TOS DO 5.3 but anyway I got the solution thanks to you. I indeed didn't think about using 2 tInputDelimited
I actually had to tweak a little the command you gave me, I set the tSetGlobalVar with those parameters : myKey, row2.newColumn1
Then used this to get the value in the tMap : (string)globalMap.get("myKey") to get the value stored in the tSetGlobalVar
=> Therefore I have a question because I saw by opening you files with editor that you call a row not connected to the tMap for transtyping with :" (row3Struct)globalMap.get("myKey")).newColumn" , in my TOS I'm not allowed to call a something else than the row(s) used as input (in you example : I would have been allowed only to use (row4Struct)) moreover I don't have to add the .column to obtain the value.
Is it a limitation due to version 5.3 ?
Many thanks,
Flo

Re: [resolved] [tFileInputDelimited] - Extract header and input as column var in rows

rbaldwin — Wed, 03 Sep 2014 18:11:07 GMT

Hello,
Glad I could be of some service.
It isn't a limitation of 5.3, I've done similar with versions as far back as 2.4.1. In my example I put the entire instance of the row into the globalMap - which is useful if you need more than one column out of it and my example was using two of them - so looking at it, on the tSetGlobalVar I set the value with the key "myKey" to be row3, which is the input that goes into the tSetGlobalVar. If your input going into the tSetGlobalVar was, say, row2 you would need to use row2 and row2Struct respectively. Hope that clarifies that.

Re: [resolved] [tFileInputDelimited] - Extract header and input as column var in rows

Anonymous — Fri, 05 Sep 2014 07:53:40 GMT

Yes, I got it. Thank you for the explanation.