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