Talend community forum / Post a reply

plegall · Topic review (newest first)

Solution with Perl as project language.

I've used a 2 jobs solution, the child job reads a single sheet in a single excel file, find itemname and itemnumber in this Excel sheet and append the information in a common output file. The father job iterates on a list of parameter files. Each parameter file redefines the context variables of the child job {filepath, sheetname, itemname_line, itemnumber_line}.

In this use case, you have an idea of what can be done in term of variable usage and the way to load it (through a file for example, the parameter filepath coming from the father job),

To make things easier to undersand, I have supposed that itemname and itemnumber are always on column B (you examples were always on column B). As itemname and itemnumber are in distinct Excel rows, I've also supposed that you have a single item per Excel file.

Code:

[pierrick@plegall] ~/dev/tos/topics/topic2348
$ perl /home/pierrick/script/exceltotext.pl --sheetname=Sheet1 in.xls
.---------------------.
|        Sheet1       |
+---+----+------------+
|   | A  | B          |
+---+----+------------+
| 1 | A1 | B1         |
| 2 | A2 | B2         |
| 3 | A3 | B3         |
| 4 | A4 | B4         |
| 5 |    | first item |
| 6 |    |          1 |
'---+----+------------'
[pierrick@plegall] ~/dev/tos/topics/topic2348
$ cat param2.txt 
filepath;/home/pierrick/dev/tos/topics/topic2348/in.xls
sheetname;Sheet2
itemname_line;8
itemnumber_line;9
[pierrick@plegall] ~/dev/tos/topics/topic2348
$ perl /home/pierrick/script/exceltotext.pl --sheetname=Sheet2 in.xls
.----------------------.
|        Sheet2        |
+---+----+-------------+
|   | A  | B           |
+---+----+-------------+
| 1 | A1 | B1          |
| 2 |    |             |
| 3 |    |             |
| 4 |    |             |
| 5 |    |             |
| 6 |    |             |
| 7 |    |             |
| 8 |    | second item |
| 9 |    |           2 |
'---+----+-------------'
[pierrick@plegall] ~/dev/tos/topics/topic2348
$ cat param3.txt 
filepath;/home/pierrick/dev/tos/topics/topic2348/in.xls
sheetname;Sheet3
itemname_line;10
itemnumber_line;11
[pierrick@plegall] ~/dev/tos/topics/topic2348
$ perl /home/pierrick/script/exceltotext.pl --sheetname=Sheet3 in.xls
.----------------------.
|        Sheet3        |
+----+----+------------+
|    | A  | B          |
+----+----+------------+
|  1 | A1 | B1         |
|  2 |    |            |
|  3 |    |            |
|  4 |    |            |
|  5 |    |            |
|  6 |    |            |
|  7 |    |            |
|  8 |    |            |
|  9 |    |            |
| 10 |    | third item |
| 11 |    |          3 |
'----+----+------------'

In the child job, the "trick" is the tPerlRow component:

Code:

$line_number = sequence('topic2348', 1, 1);

if ($line_number == $_context{itemname_line}) {
    $_globals{itemname} = $input_row[b];
}

if ($line_number == $_context{itemnumber_line}) {
    $_globals{itemnumber} = $input_row[b];
}

PS: I realize now that this is topic 2438 and not 2348, sorry about what you may see in the screenshots.

Announcement

Unanswered posts

Post a reply

Topic review (newest first)

Code:

Code:

Board footer