One Star

Processing .xls and .xlsx file in one job with one texcelinput

Hi Friends,
I am working on Excel input but my excel input can be on different format (.xls or .xlsx).
I am using texcelinput component in my job.
The issue is like....
If i have ticked the option of using Readexcel2007 file format then i can not use .xls file as input it is throwing an error.
ERROR:
Exception in component tFileInputExcel_1
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: 'D:\File\Data\CASA_ALERT.xls'
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:102)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:199)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:178)
at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:62)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:188)
at rbl.casa_alert_0_1.CASA_ALERT.tFileInputExcel_1Process(CASA_ALERT.java:5538)
at rbl.casa_alert_0_1.CASA_ALERT.tMSSqlRow_1Process(CASA_ALERT.java:1351)
at rbl.casa_alert_0_1.CASA_ALERT.runJobInTOS(CASA_ALERT.java:8210)
at rbl.casa_alert_0_1.CASA_ALERT.main(CASA_ALERT.java:8024)
My assumption is that i am using component for higher version of Excel(.xlsx),so it should be applicable for lower version of Excel which is .xls.
Can any one help me on this??
2 REPLIES
Community Manager

Re: Processing .xls and .xlsx file in one job with one texcelinput

Hi
The drivers used to read the excel with different format are different, if the file name may vary, you have to parse the file name and detect file extension first, and then use the corresponding excel component with the runIf connector to read it. For example:
tFileList--iterate--tJava--runIf_1--tFileInputExcel_1--main--tLogRow_1
--runIf_2--tFileInputExcel_1--main--tLogRow_1
on tJava: parse the current file name and detect the file extension, and put a value of boolean type to a global variable that will be used as the condition of runIf connector.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Processing .xls and .xlsx file in one job with one texcelinput

Hello
I believe he is asking if there's a component or workaround such that he only needs to use 1 component that can handle both versions (.xls AND .xlsx) without having branching actions. The component would accept both file types regardless, and then continue with the job processing.