tFileExcelSheetInput vs tFileInputExcel

[jtalend]

A job that runs fine in tFileInputExcel gives me this Exception when I process the Excel file using tFileExcelSheetInput 
The exception is in component tMap_1
java.lang.NumberFormatException
at java.math.BigDecimal.<init>(BigDecimal.java:470)
at java.math.BigDecimal.<init>(BigDecimal.java:739)
It is picking the correct columns in either case, but something in the Excel file is causing it to throw up in the second case. 
A few differences between the two components when they are being used:
- I noticed that the Read excel2007 file format(xlsx) check box is missing in the tFileExcelSheetInput component. 
- the Excel file is passed in untouched in the first component, whereas in tFileExcelSheetInput I go in and manually change some of the column names to match those specified in the schema, and then I hit the save button
I also found out that if I go into the Excel file and change the format of the money columns to be Number, with the 1000s separator unchecked, tFileExcellSheetInput processes the file just fine.
I was expecting tFileInputExcel and tFileExcelSheetInput to do their job without too much manual intervention on the input files....

[Anonymous]

Hi,
in the component tFileExcelSheetInput is no option to read the xml based Excel format because the component tFileExcelWorkbookOpen automatically detects the correct format.
I am a bit confused about the statement about an Exception in tMap and what this has to do with the tFileExcelSheetInput component. 
Could you perhaps post an screenshot of the job and a bit longer stack trace.
I would be happy to get an example file which cause problems with the tFileExcelSheetInput component. So far I do not have experienced such problems. 
In the advanced settings you can specify the language to convert text to numbers and vis versa. This affects the char for the thousands and decimal point. 

[jtalend]

It turns out the Header line had the wrong (row number) value in it. After that was fixed the component is identifying the columns by matching header column names, and it is forwarding the data as expected!
Now, is there a way to dynamically set the Excel Filename in the tFileExcelWorkbookOpen component - from, say, a context variable?  

[Anonymous]

You can assemble the file name as you like: e.g. you have the directory as a context variable and the file name too:
set as Filename (actually the full file path): context.dir + "/" + context.name
supposed to have 2 variables : dir and name.

[jtalend]

That worked. TY!
Now I am testing the component with several different spreadsheets (from various sources), and I am running into problems with dates.
My schema in tFileExcelSheetInput for all columns is set to String, which I then parse and convert appropriately in a tMap. Since different authors format dates differently in their spreadsheets, I am getting this error from a few of the spreadsheets on the date columns (in the spreadsheet the date appears as 3/30/2015)
connecting to socket on port 3846
connected
Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "20150330000000"
at routines.TalendDate.parseDate(TalendDate.java:864)
at routines.TalendDate.parseDate(TalendDate.java:808)
at me_comm1.load_tmp_commfeed_0_1.Load_TMP_CommFeed.tFileExcelSheetInput_1Process(Load_TMP_CommFeed.java:2700)
The date fields in the spreadsheet are formatted like so:

I stepped through Java debug and the date is coming in from tFileExcelSheetInput as "20150330000000".
Any suggestions?

[Anonymous]

The pattern you showed us is not the same as will be applied to parse a date. 
The Excel pattern will only be used to configure the visible value presentation in Excel it self.
The component reads the cell values with the type Excel has set for them. If you read a actual date cell as String the default date pattern will be applied to the date value to convert it into a String value. The current pattern is "yyyyMMddHHmmss" - exactly what you get but your date has obviously not a time part.
I suggest you let the component convert the cell value into a date because doing it in a tMap is unnecessary work wich can be done in a more convenient way by the component it self.

[jtalend]

That makes sense. I am now able to pull the dates all the way through.
Now on another spreadsheet I am getting a parse error on a column which I defined in the built-in Schema as BigDecimal.
Here is the stack trace in Java debug mode:
Exception in component tFileExcelSheetInput_1
java.lang.Exception: Read column MECommissionAmt in row number=1 failed:Unparseable number: "$73.95"
at me_comm1.load_tmp_commfeed_0_1.Load_TMP_CommFeed$1Helper_tFileExcelSheetInput_1.fill(Load_TMP_CommFeed.java:2436)
at me_comm1.load_tmp_commfeed_0_1.Load_TMP_CommFeed.tFileExcelSheetInput_1Process(Load_TMP_CommFeed.java:2485)
at me_comm1.load_tmp_commfeed_0_1.Load_TMP_CommFeed.tFileExcelWorkbookOpen_1Process(Load_TMP_CommFeed.java:951)
at me_comm1.load_tmp_commfeed_0_1.Load_TMP_CommFeed.tFileInputProperties_1Process(Load_TMP_CommFeed.java:821)
at me_comm1.load_tmp_commfeed_0_1.Load_TMP_CommFeed.runJobInTOS(Load_TMP_CommFeed.java:5490)
at me_comm1.load_tmp_commfeed_0_1.Load_TMP_CommFeed.main(Load_TMP_CommFeed.java:5373)
Caused by: java.text.ParseException: Unparseable number: "$73.95"
at java.text.NumberFormat.parse(NumberFormat.java:350)
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetInput.getDoubleCellValue(SpreadsheetInput.java:289)
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetInput.getDoubleCellValue(SpreadsheetInput.java:269)
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetInput.getBigDecimalCellValue(SpreadsheetInput.java:322)
at me_comm1.load_tmp_commfeed_0_1.Load_TMP_CommFeed$1Helper_tFileExcelSheetInput_1.fill(Load_TMP_CommFeed.java:2434)
... 5 more
Any ideas or suggestions?

[jtalend]

Adding test file and settings for the above parse error. Thanks
CommissionsTestFile.rar_20150727-1814.rar

[Anonymous]

I have tested it and if the cells are numeric cells which are formatted as currency it works well. I guess the problem occurs if this value is set as text value in Excel. In this case I suggest you read this cell value as String and remove in a tMap the the currency symbol and convert the cleaned value into a number.
This could be done with this expression for the output column:
 

Double.parseDouble(row1.curr_value_string.replace("$", ""))

[jtalend]

This is working as expected. Thanks