One Star

Checking if excel sheet exist

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.
15 REPLIES
Seventeen Stars

Re: Checking if excel sheet exist

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

Re: Checking if excel sheet exist

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

Re: Checking if excel sheet exist

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.
Nine Stars

Re: Checking if excel sheet exist

Go download and install tFileExcelWorkbookOpen and tFileExcelSheetList
http://www.talendforge.org/exchange/index.php
tFileExcelWorkbookOpen:
http://www.talendforge.org/exchange/tos/download.php?rid=1312
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 |
'------------+-----------+----------+---------------'
One Star

Re: Checking if excel sheet exist

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.
Seventeen Stars

Re: Checking if excel sheet exist

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

Re: Checking if excel sheet exist

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.
Seventeen Stars

Re: Checking if excel sheet exist

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

Re: Checking if excel sheet exist

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!
Seventeen Stars

Re: Checking if excel sheet exist

Thanks a lot for this problem report! I will create a test for it and solve this as soon as possible.
Nine Stars

Re: Checking if excel sheet exist

From the tFileExcelSheetList are you casting the string from the SHEET_NAME to a date?
My job looks like:
tFileExcelWorkbookOpen > tFileExcelSheetList > tMap > tTeradataOutput > tLogRow
In the tMap:
TalendDate.parseDate("yyyy", row1.SHEET_NAME );
Output:
--------------
| tLogRow_1 |
------------
|SubmissionYear|
------------
|01-01-2013 |
|01-01-2008 |
|01-01-1999 |
|01-01-2010 |
--------------
Seventeen Stars

Re: Checking if excel sheet exist

@talendtester: No he tries to read a sheet with a user defined format and my component fails in this situation: explanation here:
@_OZ_:
I have found the problem with the date as year. I guess you have formatted the cell in Excel with yyyy. This is a user defined format and excel set as cell type internal as text. The real value in excel is still the date (e.g. 01/01/2014).
The component tries to parse the content with the pattern yyyy (from the Talend schema pattern) and fails.
Now starts my "disaster recovery": The component tries to figure out what is the real format and currently does not know yyyy and e.g. MM/yyyy.
I have added these formats and the next release will read these values.
Nine Stars

Re: Checking if excel sheet exist

Excel uses the current date format of the windows operating system.
When I open Excel and press "ctrl" + ";" the following is written:
2/21/2014
This is because my current Short date is:
M/d/yyyy
When I change the Short date to:
yyyy-MM-dd
Excel automatically displays the date as:
2014-02-21
One Star

Re: Checking if excel sheet exist

@talendtester: right, Excel automatically converts date fields to a default format. But the input or output of Excel would still be the actual value ("2012" but shown as "01-01-2012" in Excel).
By the way same issue causes for strings and numbers, e.g. you output a number as string then excel could not calculate with it. So you better take care of formats in Talend to garantee in Excel works everything fine.
Nine Stars

Re: Checking if excel sheet exist

If you want the date in Talend to be only "yyyy" you can use StringHandling.LEFT() or StringHandling.RIGHT() depending on the format of your date from Excel.