Checking if excel sheet exist

[apletschette]

Hello,
I need to import several excel tables into one database. I defined the excel-filenames and sheet names in two text-files on which I iterate (tFileInputDelimited => tFlowToIterate => tFileInputDelimited => tFlowToIterate => tFileInputExcel)
The excel file generally have the same sheets but it's possible that one or the other sheet is missing in some excel-files. The result is the fatal error "Special sheets not exist".
My question: Can I make Talend ignore this error or make a check before trying to read the excel sheet?
Thanks.

[Anonymous]

hi,
use the component dedicated to this task : tFileExist
regards
laurent

[Anonymous]

answering to fast ...
not sure that you could check if a spoecific sheet exist without create some code .
May be ask for that feature at Talend jira
regards
laurent

[Anonymous]

Just found that thread. I have the same problem. How to sort out files that does not include a particular excel spread sheet? I tried to use tFilesExcelWorkbookOpen => tFileExcelSheetInput components. But here also an error occurs and the job aborted.

[talendtester]

Go download and install tFileExcelWorkbookOpen and tFileExcelSheetList
http://www.talendforge.org/exchange/index.php
tFileExcelWorkbookOpen:
https://community.talend.com/t5/Archive/performances-Talend-vs-BO-data-integrator-XI-2/td-p/167683
tFileExcelSheetList:
http://www.talendforge.org/exchange/tos/download.php?rid=910

My job looks like:
tFileExcelWorkbookOpen > tFileExcelSheetList > tLogRow

-----------+-----------+----------+--------------
|COUNT_SHEETS|SHEET_INDEX|SHEET_NAME|SHEET_ROW_COUNT|
-----------+-----------+----------+--------------
|3 |0 |Sheet1 |60 |
|3 |1 |Sheet2 |581 |
|3 |2 |Sheet3 |316 |
'------------+-----------+----------+---------------'

[Anonymous]

Sorry, but I don't understand your post...
The question is how to make sure that the job continues even when an excel file does not contain a particular spread sheet. As mentioned in my first post here, I already installed the components you mentioned but the same error occurs. In addition I could not find any helpful option.

[Anonymous]

You could iterate through the sheets and test if the one you want is present. Whats wrong with that approach?

[Anonymous]

Sorry, I didn't see that talendtester wrote FileExcelSheetList... My fault.
But I could design a job to get rid of those annoying error messages:
FileExcelWorkbookOpen => FileExcelSheetList => Filter (Filter for a particular sheet) => FlowToIterate => FileInputExcel (sheet = ((String)globalMap.get("tFileExcelSheetList_1_SHEET_NAME"))) => LogRow.
Works fine. I used FileInputExcel instead of FileExcelSheetInput because I identified some unexpected date issues.

[Anonymous]

Could you please describe what issues with date typed columns you had. I am very interested getting info about bugs.

[Anonymous]

No problem. My Input is a "year" column, e.g. "2011, 2012, 2013 etc.". When I use the FileInputExcel component I could setup the field as String or as Date (schema "yyyy"). In both cases the result in logrow is correct.
When I use the FileExcelSheetInput both variants do not work. In case of a string, my result is "2,013". In case of the date schema, I got this error:

Exception in component tFileExcelSheetInput_1
java.lang.Exception: Read column newColumn1 in row number=1 failed:SubmissionYear is an unsupported date format!
Supported formats are:
d.M.y
dd.MM.yyyy
yyyy-MM-dd
M/d/yy
MM/dd/yyyy
d.M.y HH:mm:ss
dd.MM.yyyy HH:mm:ss
yyyy-MM-ddTHH:mm:ss
d.M.y HH:mm:ss.SSS
dd.MM.yyyy HH:mm:ss.SSSSSS

Hope it helps. And thanks for your help!