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