Transforming Complex Flat File

[Anonymous]

Dear All,

I have a flat file which has format something like below,

Location{Year{Working_Days_Month1{Working_Days_Month2...{Working_Days_Month12
CHE{2019{111110011111001111100111110011{111110011111001111100111110....{111110011111001111100111110011

I need to transform the above data to a table or json as below with following columns,

Column 1 - Location

Column 2 - Year

Column 3 - Month{1-12)

Column 4 - Days (1-31)

Column 5 - Working Days of Particular Month ( 1 or 0 based on number of days present in flat file)

Could you please guide me a best approach for designing and developing this kind of transformation using talend.

Thank you in advance.

[TRF]

Get file records using tFileInputDelimited with "{" for the field separator and the following schema (to be continued with months 5 to 12):

Connect tFileInputDelimited to tSplitRow component.

Define the output schema for this component like this:

Define tSplitRow setting like thisDefine the output schema for this component like this (to be continued with months 5 to 12):

As you can see, days value for february depends on year value for leap years.

In case of records are termined by "}}}}}}}}}}}}}", just change the expression for workingDays field on line 12 with the following:

row30.workingDaysMonth12.replaceAll("}", "")

Here is the result:

.--------+----+-----+----+------------------------------.
|                      tLogRow_11                       |
|=-------+----+-----+----+-----------------------------=|
|location|year|month|days|workingDays                   |
|=-------+----+-----+----+-----------------------------=|
|CHE     |2020|1    |31  |111110011111001111100111110011|
|CHE     |2020|2    |29  |111110011111001111100111110   |
|CHE     |2020|3    |31  |111110011111001111100111110011|
|CHE     |2020|4    |30  |...                           |
'--------+----+-----+----+------------------------------'

[Anonymous]

@TRF  Thank you Very Much for your solution approach.

It partially covered my scenario, but i would like know is there any way to iterate values in working days cell to insert it as column. My out table will be something like shown in the picture.

Could you please let me know how we can iterate values within working days.

ComplexFlatFileTransformation.JPG

[TRF · Accepted solution]

Here it is:

Starting from the previous job design, add the following component after tSplitRow:

• tFlowToIterate to start iteration for each input row
  It will generate a global variable for each input field.
  Don't change anything from the default settings, the variable names will be based on the row name + the field name (for example in my case "row31.year").
• tLoop start a "for" loop from 1 to the number of days in the current month
  Just change the "To" parameter from the default settings and it with the following expression (change "row31" by your tSplitRow output name):
  

  ((Integer)globalMap.get("row31.days"))

• tIterateToFlow to generate outflow from current values
  
• tLogRow to display the result
  

  .--------+----+-----+---+----------.
  |            tLogRow_11            |
  |=-------+----+-----+---+---------=|
  |location|year|month|day|workingDay|
  |=-------+----+-----+---+---------=|
  |CHE     |2019|1    |1  |1         |
  |CHE     |2019|1    |2  |1         |
  |CHE     |2019|1    |3  |1         |
  |CHE     |2019|1    |4  |1         |
  |CHE     |2019|1    |5  |1         |
  |CHE     |2019|1    |6  |0         |
  |CHE     |2019|1    |7  |0         |
  |CHE     |2019|1    |8  |1         |
  ...
  |CHE     |2019|1    |30 |1         |
  |CHE     |2019|1    |31 |1         |
  |CHE     |2019|2    |1  |1         |
  |CHE     |2019|2    |2  |1         |
  |CHE     |2019|2    |3  |1         |
  ...
  |CHE     |2019|2    |27 |0         |
  |CHE     |2019|2    |28 |0         |
  |CHE     |2019|3    |1  |1         |
  |CHE     |2019|3    |2  |1         |
  |CHE     |2019|3    |3  |1         |
  ...
  '--------+----+-----+---+----------'

That's all.

Just take care of the input file values for "workingDaysMonth" fields which must be valid regarding on the number of "010" (could be checked after tSplitRow as at this stage you know the number of days per month including for leap years).

[Anonymous]

@TRF  That's great thank you very much