Reading values in CSV headers

[Anonymous]

I have an input CSV file that looks like this -
GBI_EMGLOBAL_DIV ,20150101
BIG ID,Local ID,Bond Description,Liquidity
XID0035,FR55,Indonesia 7.3750%  IDGB  Sep 2016,Traded
XID0041,FR60,Indonesia 6.2500%  IDGB  Apr 2017,Traded


The header has two columns in first row followed by the actual header row consisting of 4 columns and the corresponding values in row 3 onwards. I want the Output CSV to look like
BIG ID, Local ID, Bond Description, Liquidity, FileDate as column header and the values exactly same as row 3 onwards but an extra column called FileDate in the header and the value to be 20150101. This value is extracted from column 2 in row 1. Could someone please point me in right direction.

The output CSV should look like -
BIG ID, Local ID, Bond Description, Liquidity, FileDate
XID0035,FR55,Indonesia 7.3750%  IDGB  Sep 2016,Traded,20150101
XID0041,FR60,Indonesia 6.2500%  IDGB  Apr 2017,Traded,20150101

[IanM]

I've done this before by creating a tFileList with two iterate outputs. The first iterate reads the file for row one only (Header = 0, Limit = 1) and you can then set a string global variable equal to the value in Column 2 (your date).
The second iterate reads the file again, but this time with header set as 2, so it starts at BIG ID. If you then feed in to a tMap, you can insert a new column FileDate and set the value to the global variable above.
The variable is changed on each new file.
Make sure you get the iterate order correct.
Ian

[Anonymous]

Thank you I actually started something on similar. Except I used two tFileList where one outputs with Header = 0 and Limit = 1 as per your suggestion and second with Header = 2, the iterate output of both go to tFileInputDelimited. But there was inconsistency in the output file where some rows from file two has dates of file 1 etc. I am trying to get two iterate outputs out of single tFileList but the tFileInputDelimited only accepts one. Sorry this is only my first week with Talend therefore might be asking stupid questions.

[Anonymous]

Just wanted to add another way of doing this job.. 

as you have developed it using tFilelist a I would like to add some points further to that. 

redesign your job as follows. 

tFileList---iterator----tFileInputdelimited---tJavaRow---tMap---youroutput

[list=*]

In tFileInputDelimited don`t skip the header rows. 

Create context variable FileDate.

Now go to tJavaRow and add following code in it. 

if(!Relational.iSnull(input_row.FirstColumn) && input_row.FirstColumn.startsWith("GBI_EMG"))
{
   context.FileDate=input_row.SecondColumn; //you can convert it to proper date
}

[list=*]

Go to tMap and add extra column at output to create expected output file

Use context.FileDate variable to add inside FileDate column. 

inside output Filter expression you can add below filter expression to filter out header rows. 

!row1.FirstColumn.startsWith("GBI_EMG") && !row1.FirstColumn.startsWith("BIG ID")

you need to modify some of the things as per your job design and actual columns but this will give you exact output you are looking. 

[IanM]

That didn't come out as expected! I'll try again.

[IanM]

See attached images

[IanM]

                                                

[IanM]

[IanM]

[Anonymous]

Ok I managed in the end. Thanks once again for pointing me in right direction. Not sure why it didn't allow me to create two iterate outputs, I deleted the components and tried again. tFileList - Iterate1 - tfileInputDelimited and Iterate2 to another tfileInputdelimited. Output of first to globalvar and second to tmap, tmap uses the var for date, output of that goes to tUnite which merges all my input files into one and that goes to tFileOutputDelimited. Perfect!!!.