topic Re: [resolved] tFileExcelSheetInput same data in different columns in Talend Studio

[resolved] tFileExcelSheetInput same data in different columns

Anonymous — Wed, 21 Sep 2016 19:28:06 GMT

Hello community!
I'm quite new using Talend, and for solving an Excel reading problem I'm using tFileExcelSheetInput component (works very good!), but I can't make it work with a file like the image attached.
The data is separated in several and dynamic columns, each column have a number as column name (you can see the config of the component and the error message on the attached image)...
Is there any way to read this kind of format/schema?

Re: [resolved] tFileExcelSheetInput same data in different columns

Anonymous — Thu, 22 Sep 2016 04:18:18 GMT

Hi,
tFileExcelSheetInput is a custom component written by talend community user and shared on talend exchange portal.
Feel free to contact the author jlolling directly.
Best regards
Sabrina

Re: [resolved] tFileExcelSheetInput same data in different columns

Anonymous — Thu, 22 Sep 2016 13:46:24 GMT

Thank you Xiado Shi, I already did that!
I'll share the answer here.

Re: [resolved] tFileExcelSheetInput same data in different columns

Anonymous — Thu, 22 Sep 2016 14:29:11 GMT

I will take a look at it. Actually it should work.

Re: [resolved] tFileExcelSheetInput same data in different columns

Anonymous — Fri, 23 Sep 2016 10:48:10 GMT

Sorry for the late answer. NO it cannot work, because the regex expression must select exactly one distinct column and your expression points to multiple columns. There is no kind of iteration implemented. I will improve the error message because indeed this message is misleading in this case.

Re: [resolved] tFileExcelSheetInput same data in different columns

Anonymous — Fri, 23 Sep 2016 13:05:55 GMT

Ok,
Thank you Jan. In your experience, what can I do for read this kind of Excel?

Re: [resolved] tFileExcelSheetInput same data in different columns

Anonymous — Fri, 23 Sep 2016 13:21:51 GMT

Are your columns always differently named (always different numbers) ?
If you want to read the column e.g 5 always for the same schema column, simply type 5 as column name (in this case you do not need regex).
If you want to read always the same excel column - regardless what number is set as header then address the column fix.
or you have to explain a bit more how your use case should work.

Re: [resolved] tFileExcelSheetInput same data in different columns

Anonymous — Fri, 23 Sep 2016 14:00:32 GMT

Ok,
Columns names are always numbers in sequence: 1, 2, 3..., n (dynamic)
Each column have 18 rows (excluding header).

I need to read for every column, every row. In order tu put the data in a database with this columns:

instrumentDate | instumentName | columnNumber | tickerName

Using the image from my first post, I should get something like this:

13-09-2016|US1234567890|1|Data 1
13-09-2016|US1234567890|1|Data 2
13-09-2016|US1234567890|1|Data 3
13-09-2016|US1234567890|1|Data 4
13-09-2016|US1234567890|1|Data 5
13-09-2016|US1234567890|1|Data 6
13-09-2016|US1234567890|1|Data 7
13-09-2016|US1234567890|1|Data 8
13-09-2016|US1234567890|1|Data 9
13-09-2016|US1234567890|1|Data 10
13-09-2016|US1234567890|1|Data 11
13-09-2016|US1234567890|1|Data 12
13-09-2016|US1234567890|1|Data 13
13-09-2016|US1234567890|1|Data 14
13-09-2016|US1234567890|1|Data 15
13-09-2016|US1234567890|1|Data 16
13-09-2016|US1234567890|1|Data 17
13-09-2016|US1234567890|1|Data 18
13-09-2016|US1234567890| 2|Data 19
13-09-2016|US1234567890| 2|Data 20
13-09-2016|US1234567890| 2|Data 21
13-09-2016|US1234567890| 2|Data 22
...

Re: [resolved] tFileExcelSheetInput same data in different columns

Anonymous — Fri, 23 Sep 2016 16:27:10 GMT

I make it work with a tLoop. Is not an elegant solution but it's work (I'll take a better look later).
Now I have to get A1 and B1 cells, and I do it in a different flow... But now I can't "merge" this two flows (I try with unite and tmap)... what do I missing?