If not, you will have to rethink this. Although it might take a while, the files will become unmanageable. It's best to consider these things now. Also, if you implement some "Max number of quarters" logic, it makes the Talend solution workable.
OK, this might work.....but you will have to play around to perfect it.
1) Convert your Excel file to CSV
2) Use a tFileInputFullRow component to bring in one row at a time.
3) Use a tJavaFlex to extract the Country column (and any other non period columns) and to output the collection of period columns as one column. This will require String manipulation. Your data types will be lost, but you can get these back later once your columns are split. You will have to use this component to also extract the Period Names from the header row. This will be a bit more complicated, but can be done quite easily in the tJavaflex if you take a copy of the first row (the header row) and process it alongside each data row.
An example of the sort of code you will need to use to mix the header data with the row data is below....
Start Code
String header = null;
Main Code
//If header is null (first row) set the header value
if(header==null){
header = row1.myColumn;
}else{ //Process value rows
String[] columns = header.split(",");
String[] values = row1.myColumn.split(",");
String outputString = "";
//Merge the header info (Periods) with the values for the periods
//This is done to enable the tNormalize component to split into
//Period|Value columns over multiple rows
for(int i = 0; i< columns.length; i++){
outputString = outputString+columns[i]+"|"+values[i]+",";
}
outputString = outputString.substring(0, outputString.length()-1);
row2.output = outputString;
}
The above does not cover the separating out of the Country data, etc, but you can figure that out. It is comparatively easy.
4) Use a tNormalize to process your data into rows. You are splitting the data into Country, Period Key/Value rows.
5) Add a tMap to split the Period Key/Value column into two columns. Split using String manipulation with the "|" as the separator.
Once you are at this stage, your data is in the format you want. You just need to worry about data types. You can use a tConvertType for that.
I'm not sure what you mean. If it requires a different solution than the one proposed here, can you raise a different post and give a detailed explanation of the problem please? This will help people who have a similar question to you, find an answer.
