[resolved] tFileExcelSheetInput same data in different columns

Highlighted
One Star

[resolved] tFileExcelSheetInput same data in different columns

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?


Accepted Solutions
Moderator

Re: [resolved] tFileExcelSheetInput same data in different columns

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

All Replies
Moderator

Re: [resolved] tFileExcelSheetInput same data in different columns

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] tFileExcelSheetInput same data in different columns

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

Re: [resolved] tFileExcelSheetInput same data in different columns

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

Re: [resolved] tFileExcelSheetInput same data in different columns

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.
One Star

Re: [resolved] tFileExcelSheetInput same data in different columns

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

Re: [resolved] tFileExcelSheetInput same data in different columns

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. 
One Star

Re: [resolved] tFileExcelSheetInput same data in different columns

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
...
One Star

Re: [resolved] tFileExcelSheetInput same data in different columns

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?


Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog