[resolved] Mapping multiple sheets excel

[Anonymous]

Hi,
I have a job,
 
For example: I have 2 files excel( File1, File2) and it has 2 sheets(X,Y)
- tfilelist_1 read all files in folder Temp
- tfilelist_2 read all files in folder Temp
- A_excel read sheet X
- B_excel read sheet Y
- tMap_1 mapping 2 sheets
- A,B,C,D are ouput
When i ran this job, i saw only sheets of file2 will be map.
But expected results is: They are 2 files output:
- output_1: sheet X of file1 will connect sheet Y file1
- output_2: sheet X of file2 will connect sheet Y file2
Can you help me review and give me feedback?
Moon. 

[Anonymous]

Hi
Why don't you check the 'all sheet' box on tFileInputExcel to read data from all sheets? To be honest, I don't understand your requirement, can you please give us some example data to explain your request and what are you expected result?
Best regards
Shong

[Anonymous]

Thank you so much Shong, i can't read all sheets because 2 sheets with difference column.
For example data:

Sheet1 of file1

Id|name
1|A
2|B
3|C

Sheet2 of file1

age|year|birthay
18|2000|1-1

Sheet1 of file2

Id|name
4|D
5|E
6|F

Sheet2 of file1

age|year|birthay
20|1998|3-1
Expected results:

output_1:

Id|name_age|name
1|A_18|A
2|B_18|B
3|C_18|C

ouput_2

Id|name_age|name
1|C_20|C
2|D_20|D
3|E_20|E
If you have any question let me know!
Moon.

[Anonymous · Accepted solution]

Hi  
I understand your requirement right now. You just need to perform a join between sheet1 and sheet2 of each file. The job design looks like:
parent job:
tFileList--iterate---tRunJob
on tRunJob, call the child job and pass the current file path to child job, see this article to learn how to pass a value from the child job.
child job:
tFileInputExcel_1--main--tMap--out1-->tFileOutputDelimited
                                      |
                                  lookup
                                      |
                                  tFileInputExcel_2
In child job, define a context variable called filepath for used to receive the current file path from parent job. 
tFileInputExdel_1: read sheet 1, set the File name with context.filepath
tFileInputExdel_1: read sheet 2, set the File name with context.filepath
on tFileOutputDelimited, define a dynamic output file path for each output result, eg:
"d:/file/"+context.filepath+".txgt"
Best regards
Shong

[Anonymous]

thanks Shong, But job can't run by .bat file or .sh file. I think, child job and main job can't connect context tfilelist? Please give me your feedback.
Best regard,
Moon.

[Anonymous]

Sorry Shong, i will try with the same job, it work .

[Anonymous]

great, thanks for your feedback!
Shong