[resolved] Excel transformation

Highlighted
One Star

[resolved] Excel transformation

   I have 10 excel file(one for each year from 2005 to 2014 ) with the same structure as the first one(input), everyone contains the number of male, female and total that live in a city, for each single year of age(0,1,2...100) (there are the same 30 cities for all files) .
I have to merge all the input file in one output that contains 6 columns : city/age/m/f/t/year like in the figure.
Some suggestion?What component should I use to make the transformation and the merge?
Thanks to all! Smiley Happy
Marco

Accepted Solutions
One Star

Re: [resolved] Excel transformation

Thanks it works! 
Just one more thing... I put all the input files in a directory, so my job is like:
tFileList--> tFileInputExcell-->tMap--->Toutput   setting city as key...But the tMap, insted of add every file to the out, substite every file. The result is that in output I have only the data about the last file(2014)... Where I am wrong? Smiley Frustrated 

edit I haven't checked "Append existing file" ahahah resolved

All Replies
Seventeen Stars

Re: [resolved] Excel transformation

I suggest you use a tMap with an normal output (out0) with your output schema and add a joined output (out1) to it.
If you create an output in a tMap you will always ask for the kind of output, here choose joined output to build out1.
First map the m f t columns from the 0 section to the out0 columns (and all other too) and set as age fix 0
Next map the m f t columns from the 1 section to the out1 columns (and also all others too) and set as age fix 1.
A joined output sends its output to the physical output (out0). Its like a union.
One Star

Re: [resolved] Excel transformation

Thanks it works! 
Just one more thing... I put all the input files in a directory, so my job is like:
tFileList--> tFileInputExcell-->tMap--->Toutput   setting city as key...But the tMap, insted of add every file to the out, substite every file. The result is that in output I have only the data about the last file(2014)... Where I am wrong? Smiley Frustrated 

edit I haven't checked "Append existing file" ahahah resolved

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog