topic Re: Reading values in CSV headers in Talend Studio

Reading values in CSV headers

Anonymous — Mon, 10 Aug 2015 15:36:27 GMT

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

Re: Reading values in CSV headers

IanM — Mon, 10 Aug 2015 20:15:46 GMT

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

Re: Reading values in CSV headers

Anonymous — Tue, 11 Aug 2015 09:52:16 GMT

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.

Re: Reading values in CSV headers

Anonymous — Tue, 11 Aug 2015 10:26:12 GMT

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.

Re: Reading values in CSV headers

IanM — Tue, 11 Aug 2015 10:26:26 GMT

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

Re: Reading values in CSV headers

IanM — Tue, 11 Aug 2015 10:32:51 GMT

See attached images

Re: Reading values in CSV headers

IanM — Tue, 11 Aug 2015 10:37:09 GMT

Re: Reading values in CSV headers

IanM — Tue, 11 Aug 2015 10:38:22 GMT

Re: Reading values in CSV headers

IanM — Tue, 11 Aug 2015 10:40:17 GMT

Re: Reading values in CSV headers

Anonymous — Tue, 11 Aug 2015 10:53:24 GMT

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