Four Stars

Need help on reading complex excel source

In one of our job the source is excel.

It should read the data in a set of rows and columns, additionally it needs to read particular cell, file name, sheet name, row number as additional columns.

 

Below is the scenario:

It needs to read the data range from D5:Q6, additionally it reads C9 cell value, D2 cell value, file name, sheet name, row number as additional columns.

 

In the component, I can read data range and can get the sheet name if we process sheets individually. We are in a process to determine how to read C9 cell value, D2 value, file name, sheet name, row number and append as additional columns.

 

Below is the example:

 

Excel Source:

Data Range(D5:G6) with below values

D5

E5

F5

G5

D6

E6

F6

G6

 

Cell Value in C9 is C9, Cell Value in D2 is D2

 

File Name as Excel

Sheet Name as sheet1

 

The output should as below:

 

D5

E5

F5

G5

C9

D2

Excel

Sheet1

5

D6

E6

F6

G6

C9

D2

Excel

Sheet1

6

 

5 and 6 are the row numbers of the excel.

 

The above example is for one sheet. But it should read multiple sheets within excel.

Could you please guide me how to approach this scenario.

Thanks in advance.

Tags (1)
1 REPLY
Moderator

Re: Need help on reading complex excel source

Hello,

Here is a custom component tFileExcelSheetInput which is written by talend community user and shared on talend exchange portal. This custom component reads an Excel sheet with lots of comfort functions
* Automatic adjustment of the columns to read
* Read comments
* Read cell style
* Read and interpret date formats very tolerant
* Read only the columns you need
* Can skip erroneous cell content 

Please take a look at this custom component:https://exchange.talend.com/#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%...

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.