topic Re: Checking if excel sheet exist in Talend Studio

Checking if excel sheet exist

apletschette — Wed, 27 Nov 2013 09:14:01 GMT

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.

Re: Checking if excel sheet exist

Anonymous — Wed, 27 Nov 2013 11:26:54 GMT

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

Re: Checking if excel sheet exist

Anonymous — Wed, 27 Nov 2013 11:40:46 GMT

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

Re: Checking if excel sheet exist

Anonymous — Thu, 20 Feb 2014 12:25:00 GMT

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.

Re: Checking if excel sheet exist

talendtester — Thu, 20 Feb 2014 17:20:53 GMT

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 |
'------------+-----------+----------+---------------'

Re: Checking if excel sheet exist

Anonymous — Fri, 21 Feb 2014 08:23:47 GMT

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.

Re: Checking if excel sheet exist

Anonymous — Fri, 21 Feb 2014 08:28:15 GMT

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

Re: Checking if excel sheet exist

Anonymous — Fri, 21 Feb 2014 11:50:26 GMT

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.

Re: Checking if excel sheet exist

Anonymous — Fri, 21 Feb 2014 12:43:32 GMT

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

Re: Checking if excel sheet exist

Anonymous — Fri, 21 Feb 2014 12:56:21 GMT

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!

Re: Checking if excel sheet exist

Anonymous — Fri, 21 Feb 2014 17:09:12 GMT

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

Re: Checking if excel sheet exist

talendtester — Fri, 21 Feb 2014 18:11:15 GMT

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

Re: Checking if excel sheet exist

Anonymous — Fri, 21 Feb 2014 18:24:09 GMT

@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.

Re: Checking if excel sheet exist

talendtester — Fri, 21 Feb 2014 18:34:48 GMT

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

Re: Checking if excel sheet exist

Anonymous — Mon, 24 Feb 2014 12:35:33 GMT

@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.

Re: Checking if excel sheet exist

talendtester — Mon, 24 Feb 2014 16:03:42 GMT

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.