In the scenaio we are combining several excel workbook or sheets inside the workbook by using tUnite component .
There are two xlsx files one contain the data of France countries with 2 rows and another file contains the data of Mexico countries with 3 rows .tUnite component will merge both the files and get the data of both the countries with output containing 5 rows.
This job will read both the files from tFileInputExcel.
Combine the files with tUnite component.
tUnite component is used to merge data from various sources, based on a common schema.
For the tUnite component you need to match the schema.
This is the Excel workbook consisting of different sheets France.xlsx,Mexico.xlsx.
This is France data:--
Customer_ID|Customer_First_Name|Customer_Last_Name|Customer_Address|Customer_City| Customer_PostalCode|Customer_Country|RegisterDate
7|Justin|Ash|90 Ruapehu Road Ohakune |Strasbourg|67000|France|1/13/2010
9|David|French|Williamstown Road|Marseille|45668|France|3/16/2000
This is Mexico data:--
Customer_ID|Customer_First_Name|Customer_Last_Name|Customer_Address|Customer_City| Customer_PostalCode|Customer_Country|RegisterDate
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
13|George|Loomis|16 Martel Beloeil|Mexico|43256|Mexico|1/13/2010
Drag and drop the tFileInputExcel ,tUnite and tLogRow component from the palette.
Connect each component as shown in the below screenshot.
Open the component properties of France[tFileInputExcel_1] and Mexico[tFileInputExcel_2].In both tFileInputExcel do the same process.
Tick the box of Read excel 2007 file format.
In the File name/Stream tab provide the path of the file where you have stored the file.
To apply header to each sheet tick the box of Affect each sheet(header&footer).
Tick the checkbox of Die on error so that if there is any error while processing the file then it will stop the job.
Open the component properties of tUnite .
Here provide the same schema of France country .Simply drag and drop the schema.
In the Basic settings view of tLogRow, select the Basic option or tou can select Table option to display properly the output values.
Run the job.
Your output will look like below containing the data of both the countries France and Mexico with total 5 rows.
Starting job tunite at 13:51 17/06/2015.
[statistics] connecting to socket on port 3662
[statistics] connected
Customer_ID Customer_First_Name Customer_Last_Name Customer_Address Customer_City Customer_PostalCode Customer_Country RegisterDate
7|Justin|Ash|90 Ruapehu Road Ohakune |Strasbourg|67000|France|1/13/2010
9|David|French|Williamstown Road|Marseille|45668|France|3/16/2000
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
13|George|Loomis|16 Martel Beloeil|Mexico|43256|Mexico|1/13/2010
[statistics] disconnected
Job tunite ended at 13:51 17/06/2015. [exit code=0]
There are two xlsx files one contain the data of France countries with 2 rows and another file contains the data of Mexico countries with 3 rows .tUnite component will merge both the files and get the data of both the countries with output containing 5 rows.
This job will read both the files from tFileInputExcel.
Combine the files with tUnite component.
tUnite component is used to merge data from various sources, based on a common schema.
For the tUnite component you need to match the schema.
This is the Excel workbook consisting of different sheets France.xlsx,Mexico.xlsx.
This is France data:--
Customer_ID|Customer_First_Name|Customer_Last_Name|Customer_Address|Customer_City| Customer_PostalCode|Customer_Country|RegisterDate
7|Justin|Ash|90 Ruapehu Road Ohakune |Strasbourg|67000|France|1/13/2010
9|David|French|Williamstown Road|Marseille|45668|France|3/16/2000
This is Mexico data:--
Customer_ID|Customer_First_Name|Customer_Last_Name|Customer_Address|Customer_City| Customer_PostalCode|Customer_Country|RegisterDate
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
13|George|Loomis|16 Martel Beloeil|Mexico|43256|Mexico|1/13/2010
Drag and drop the tFileInputExcel ,tUnite and tLogRow component from the palette.
Connect each component as shown in the below screenshot.
Tick the box of Read excel 2007 file format.
In the File name/Stream tab provide the path of the file where you have stored the file.
To apply header to each sheet tick the box of Affect each sheet(header&footer).
Tick the checkbox of Die on error so that if there is any error while processing the file then it will stop the job.
Open the component properties of tUnite .
Here provide the same schema of France country .Simply drag and drop the schema.
Run the job.
Your output will look like below containing the data of both the countries France and Mexico with total 5 rows.
Starting job tunite at 13:51 17/06/2015.
[statistics] connecting to socket on port 3662
[statistics] connected
Customer_ID Customer_First_Name Customer_Last_Name Customer_Address Customer_City Customer_PostalCode Customer_Country RegisterDate
7|Justin|Ash|90 Ruapehu Road Ohakune |Strasbourg|67000|France|1/13/2010
9|David|French|Williamstown Road|Marseille|45668|France|3/16/2000
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
13|George|Loomis|16 Martel Beloeil|Mexico|43256|Mexico|1/13/2010
[statistics] disconnected
Job tunite ended at 13:51 17/06/2015. [exit code=0]
No comments:
Post a Comment