topic Re: Exception in component tFileInputExcel_1 in Talend Studio

Exception in component tFileInputExcel_1

Anonymous — Thu, 17 Nov 2016 05:52:22 GMT

I am getting one of two errors in a tfileInputExcel component when trying to read an excel file into a t-map. The ftp_get is grabbing the excel file from an ftp and placing it in the remote directory as expected, but I get the error when trying to read it. I have tried using a specific file and file stream and get the error either way. I have several other jobs set up that work the same way and work with no issues, all using a context variable for the directory path. The only difference with this job is am using two variables current year and current month to determine which subfolder to look for the files on in the ftp. The file structure is Vendor Name>>Year>>Month>> files
I'm using the global variable component to create the variables
key="current_year" value = TalendDate.getDate("CCYY")
key="current_month" value = TalendDate.getDate("MMM")
from there i use tftpfile_list>>iterate to flow>>
and using the following logic to determine which subfolder to iterate over (in this case i am looking in the 2016>>NOV folder
remote directory = "/users/finance/vendors/dms/" + ((String)globalMap.get("current_year")) + "/" + ((String)globalMap.get("current_month"))
I determine which file to grab based on a history table and then use ftpget>>tfileinputexcel
error messages

job flow

Any assistance is greatly appreciated!!

Re: Exception in component tFileInputExcel_1

vapukov — Thu, 17 Nov 2016 08:03:33 GMT

I not sure for 100% (generally text of error different), but it exactly have problem when selected wrong type of excel
In You case - checkbox "read xlxs", but error about opening file.xls
check - what version of excel file You try to open, try to test with unchecked check-box

Re: Exception in component tFileInputExcel_1

Anonymous — Thu, 17 Nov 2016 19:19:07 GMT

Hi there,
I have tried checking and unchecking the box the read xlsx. When it is unchecked I get the following error. Any other ideas?
Exception in component tFileInputExcel_1
java.lang.ArrayIndexOutOfBoundsException: 10
at jxl.read.biff.Window2Record.<init>(Window2Record.java:95)
at jxl.read.biff.SheetReader.read(SheetReader.java:542)
at jxl.read.biff.SheetImpl.readSheet(SheetImpl.java:716)

Re: Exception in component tFileInputExcel_1

vapukov — Thu, 17 Nov 2016 23:47:36 GMT

look at this (if not do before) - https://community.talend.com/t5/Design-and-Development/quot-ArrayIndexOutOfBoundsException-quot-while-using-quot/td-p/107997
try to test next variant - open this file in excel and save it in last format(some other) and re-check job with single file only (make a txt job just from 1 component tLog)
it give information for feature steps. On one project we found similar problem, and after long investigation found single person in company who use outdated excel and some time edit files - upgrade his office and problem gone

Re: Exception in component tFileInputExcel_1

Anonymous — Sun, 20 Nov 2016 22:25:50 GMT

Hi there,
I have replaced the tfileinputexcel with the custom components tFileExcelWorkBook & tFileExcelSheetInput.
However, I am now getting the following error when trying to read the file:
Exception in component tFileExcelWorkbookOpen_1
org.apache.poi.hssf.OldExcelFormatException: The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)
It looks like its the excel version is older? What can I do to be able to read it? The files are being generated from an outside vendor