topic Re: [resolved] Mapping multiple sheets excel in Talend Studio

[resolved] Mapping multiple sheets excel

Anonymous — Wed, 05 Aug 2015 06:28:27 GMT

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.

Re: [resolved] Mapping multiple sheets excel

Anonymous — Wed, 05 Aug 2015 08:43:00 GMT

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

Re: [resolved] Mapping multiple sheets excel

Anonymous — Wed, 05 Aug 2015 10:39:46 GMT

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.

Re: [resolved] Mapping multiple sheets excel

Anonymous — Wed, 05 Aug 2015 11:19:32 GMT

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

Re: [resolved] Mapping multiple sheets excel

Anonymous — Thu, 06 Aug 2015 09:33:12 GMT

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.

Re: [resolved] Mapping multiple sheets excel

Anonymous — Thu, 06 Aug 2015 11:27:13 GMT

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

Re: [resolved] Mapping multiple sheets excel

Anonymous — Fri, 07 Aug 2015 06:07:30 GMT

great, thanks for your feedback!
Shong