TOS tfileinputexcel import visible sheet only

[Echo_Ma]

Dear all,

I'm new to Talend and would request your help for below issue.

My project needs to import data from visible sheets in excel files unfortnately all sheets data are imported , including data from hidden sheets. Now we're using TOS 7.2 - tFileInputExcel to perform data extraction.

Is there any solution to ignore the data from hidden sheets and load data from all visible sheets in excel file? or Is it possible to derive the hidden/visible status of a sheet in excel file when importing data?

Thanking in advance for your kindly help!

Echo

[Anonymous]

Hi

Take a look at this KB article,

https://community.talend.com/s/article/How-to-Ignore-Hidden-Sheets-in-tFileInputExcel-Component-cZ51...

Please try the workaround and let me know if it works.

Regards

Shong

[vikramk]

Hi @Shicong Hong​ 

The given link is invalid, could you please recheck once again

[Anonymous]

Hi

Sorry, this article is still under review and is not published yet. I copy the content and paste it here.

Problem Description

When using a tFileInputExcel component, you can indicate the sheet to read by providing the related position in the Excel file (Uncheck All sheets , and in the sheet list just provide the position of the sheet list).

However, when having hidden sheets in the Excel file, this position might be wrong.

Therefore, the question is to know how to have an option 'Ignore Hidden Sheet' so that

these hidden are not taken into account and are not affecting the position number that is provided

in tFileInputExcel component. 

Solution

 - An enhancement request has been logged against this issue :

TUP-18910 Ignore Hidden Sheets Option in tFileInputExcel Component

- An option to workaround this issue is to remove the hidden sheets in in the Excel file 

and, then to perform the Talend job treatment with tFileInputExcel component .

As example, there is a quick way to remove all the hidden sheets :

1. Press Alt + F11 (in MS Excel ) to go to vba

2. Click Insert > Module

3. Copy > Paste the sub below (everything between the dotted lines)

into the white empty space on the right-side

------------begin vba----------

Sub DeleteHiddenSheets()

'Run on a *back-up* copy

'Deletes all hidden sheets

'without any prompts

Dim s As Worksheet

Application.DisplayAlerts = False

For Each s In ActiveWorkbook.Worksheets

If s.Visible = False Then

s.Delete

End If

Next

Application.DisplayAlerts = True

End Sub

--------end vba---------

4. Press Alt + Q to return to excel

5. Press Alt + F8 (or click Tools > Macro > Macros)

In the dialog box:

Click on "DeleteHiddenSheets" > Run

(or just double-click on DeleteHiddenSheets)

[Echo_Ma]

Dear ​shong (Talend),

Many thanks for your kindly help!

Checked the workaround, however it doesn't siute my scenario. Let me explain why.

My project is to collect manually input data from various retails stores - kind of loose partnership - using pre-defined standard template then perform data analysis of retail sales. However due to some reason, my customer has very limited power to make stores exactly follow the rules of data input. That's why we're currently facing many data quality issues when dealing data input by stores. One of the problem is not using the given excel template. Means that some stores could ignore the given new templete with vba workaround.

That's why I'm seeking a solution from Talend side.

If I understand correctly, in future, an enhancement "Hidden Sheets Option" would be added into tFileInputExcel . Not sure when it would be ready, but that's what I need.

Considering the tight project schedule, we've use other workaround by defining certain rules to flag data records which could filter out most of data in hidden sheets.

Future we would modify the talend solution once the enh in-place.

Thanking again for the help!

Echo

[Echo_Ma]

Dear vikramk (Customer),

Thank you very much for your kindly help! Please see my previous answer to shong.

Have a nice day.

Echo