topic Re: Transforming Complex Flat File in Talend Studio

Transforming Complex Flat File

Anonymous — Sat, 16 Nov 2024 04:27:54 GMT

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)

Re: Transforming Complex Flat File

TRF — Sat, 05 Oct 2019 11:37:50 GMT

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  |...                           |
'--------+----+-----+----+------------------------------'

Re: Transforming Complex Flat File

Anonymous — Sun, 06 Oct 2019 07:10:51 GMT

@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

Re: Transforming Complex Flat File

TRF — Sun, 06 Oct 2019 11:06:08 GMT

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).

Re: Transforming Complex Flat File

Anonymous — Sun, 06 Oct 2019 13:27:24 GMT

@TRF That's great thank you very much