How to get reject data using tFilterRow in Talend !!

In a given scenario there is a Customer data belonging to different country we have to put each customer data with there country in different excel sheets and in one excel sheet with sub sheets.

This is Customers Data Input File:--

Customer_ID,Customer_First_Name,Customer_Last_Name,Customer_Address,Customer_City,Customer_PostalCode,Customer_Country,RegisterDate

1,Lee,Sime,722 Bur Oak Avenue ,Berlin,12232,Germany,1/13/2010

2,William,Hinkle,543 ANJOU ANJOU H1K 2P4,Mexico,71450,Mexico,10/4/2007

3,Nick,Petrakis,40  Toronto M8Z 3Z7,Mexico,48577,Mexico,3/16/2000

4,Jian,Paysnoe,11 Collaroy  2093,London,56008,UK,1/13/2010

5,Min,Parks,223 Wellington  5011,Berlin,32456,Germany,10/4/2007

6,Joseph,guo,324 Tman Street   Stafford Heights ,Mannheim,43567,Germany,3/16/2000

7,Justin,Ash,90 Ruapehu Road   Ohakune ,Strasbourg,67000,France,1/13/2010

8,Ming,Ho,32 Wilfred road,Madrid,33456,Spain,10/4/2007

9,David,French,Williamstown Road,Marseille,45668,France,3/16/2000

10,GORDON,baleri,East 7th Avenue   Vancouver Vancouver,Tsawassen,98977,Canada,1/13/2010

11,Aaron,Vanzin,431 algary Calgary ,London,56785,UK,10/4/2007

12,Gregory,wang,29 View Royal Ave   Victoria Victoria,Buenos Aires,54367,Argentina,3/16/2000

13,George,Loomis,16 Martel   Beloeil,Mexico,43256,Mexico,1/13/2010

14,JACOB,Jiang,13 Surrey Close,Bern,35843,Switzerland,10/4/2007

Sync Columns to propogate the metadata.

• In the Conditions table, fill the filtering parameters.
• In InputColumn, select Customer_Country, Function as Empty,Operator as Equals.
• Or you can select other options from dropdown list such as greater than,lower based on your requirement in Operator field.

• In the Value column, type "UK" to filter .

Open the component properties of UK(tFileOutputExcel).
Tick the check box of Write excel2007 file format and Include header.
In the File Name tab provide the path where you want to store the data of UK country.

And do the same process for other countries also.

Here in tFilterRow_4 we are taking three countries Germany Spain and Canada together so here we will use option Use advanced mode then click check box and type in the following regular expression that includes Country to be searched---

input_row.Customer_Country.equals("Germany") ||
input_row.Customer_Country.equals("Spain") || 
input_row.Customer_Country.equals("Canada")

Click on the tFileList component properties and then click on “Directory” tab select the directory that contains the excel files by clicking on the “…” button.For example I have all four files in one folder named as customer_reject so provide the path of this folder.

In the "Files" column write the Filemask such as "U*" , "Fran*","Mexico*","Germany*" this means that the filename which starts from this words that files will be only considered in tFileList .

Open the tFileInputEcxel_1 component properties and set Property type  to “Built In”.
Under “File name / Stream” tab type 
((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))
Or you can type tfilelist then press ctrl + space.
Then select this option tFileList_1.CURRENT_FILEPATH.

Open tmap properties here input column with output column auto map is done each input column is connected to its output column.Click ok button.

Open the tFileInputEcxel_3 component properties and set Property type  to “Built In”.
Give the File Name path where you want to store all the countries together in excel sheet with sub sheets for eg, I have given the folder name country_all.

Under “Sheet Name” tab type 
StringHandling.EREPLACE(((String)globalMap.get("tFileList_1_CURRENT_FILE")),".xlsx","")

When you will Run the job firstly each country will be in your directory path and then from there tfilelist component will check eack country and will output each countries in one excel sheets named Country_all with sub countries.As you can see in screenshot below country_all.xlsx there are sub sheets named as France ,GermanySpainCanada ,Mexico,UK.