topic Appending same excel file with different sheet in Talend Studio

Appending same excel file with different sheet

Anonymous — Sat, 16 Nov 2024 07:38:31 GMT

I want to append data to existing excel file in new sheet.How I can achieve that ?

but i am getting below error.

[statistics] connecting to socket on port 3854
[statistics] connected
Exception in component tFileOutputExcel_3 (ExtractMetadata)
org.apache.poi.EmptyFileException: The supplied file was empty (zero bytes long)
at org.apache.poi.util.IOUtils.peekFirstNBytes(IOUtils.java:65)
at org.apache.poi.util.IOUtils.peekFirst8Bytes(IOUtils.java:48)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:177)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:144)
at org.talend.ExcelTool.prepareXlsxFile(ExcelTool.java:114)
at local_project.extractmetadata_0_1.ExtractMetadata.tSalesforceInput_1Process(ExtractMetadata.java:6322)
at local_project.extractmetadata_0_1.ExtractMetadata.runJobInTOS(ExtractMetadata.java:8265)
at local_project.extractmetadata_0_1.ExtractMetadata.main(ExtractMetadata.java:8114)
[statistics] disconnected

Job ExtractMetadata ended at 15:01 17/09/2018. [exit code=1]

Re: Appending same excel file with different sheet

fdenis — Mon, 17 Sep 2018 11:04:19 GMT

you cannot open the same excel file 2 times in the same subjob.
split creation of sheet and sheet 2 into 2 distinct subjob linked by on subjobOk
Regards,

Re: Appending same excel file with different sheet

Anonymous — Mon, 17 Sep 2018 11:22:27 GMT

I have tried two jobs..but getting below error.

Starting job ExtractMetadata at 15:50 17/09/2018.

[statistics] connecting to socket on port 3825
[statistics] connected
Exception in component tFileInputExcel_1 (ExtractMetadata)
org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)
at org.apache.poi.openxml4j.opc.internal.ZipHelper.verifyZipHeader(ZipHelper.java:184)
at org.apache.poi.openxml4j.opc.internal.ZipHelper.openZipFile(ZipHelper.java:269)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:134)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:117)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:257)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:187)
at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:90)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:336)
at local_project.extractmetadata_0_1.ExtractMetadata.tFileInputExcel_1Process(ExtractMetadata.java:5982)
at local_project.extractmetadata_0_1.ExtractMetadata.tSalesforceInput_1Process(ExtractMetadata.java:3543)
at local_project.extractmetadata_0_1.ExtractMetadata.runJobInTOS(ExtractMetadata.java:9587)
at local_project.extractmetadata_0_1.ExtractMetadata.main(ExtractMetadata.java:9436)
[statistics] disconnected

Job ExtractMetadata ended at 15:50 17/09/2018. [exit code=1]

tal4.JPG

Re: Appending same excel file with different sheet

Anonymous — Mon, 17 Sep 2018 11:44:16 GMT

I found the issue..i have one more doubt ..can u please tell ..how to get file name in tfileinputexcel using gobal map ...with out specifying..

Re: Appending same excel file with different sheet

fdenis — Mon, 17 Sep 2018 12:40:00 GMT

it's not into a globalMap value because you know it.
you have to set it into component parameter.

Re: Appending same excel file with different sheet

Anonymous — Mon, 17 Sep 2018 13:23:34 GMT

even though i got correct output,,,,still I am getting below error.

[statistics] connecting to socket on port 3897
[statistics] connected
The cell format is not Date in ( Row. 1 and ColumnNum. 3 )

tal5.JPG

Re: Appending same excel file with different sheet

fdenis — Mon, 17 Sep 2018 13:43:42 GMT

check if there is date format on row1 convert it to string.
Or add tlog on row1 to check date format
check row1 on output component.

Re: Appending same excel file with different sheet

Anonymous — Mon, 17 Sep 2018 14:24:13 GMT

still getting error...please check attachment

tal6.JPG

Re: Appending same excel file with different sheet

fdenis — Mon, 17 Sep 2018 14:37:24 GMT

set Date type to String.
it will work

Re: Appending same excel file with different sheet

Anonymous — Mon, 17 Sep 2018 15:13:44 GMT

even after changing datatype aslo getting same error..

Re: Appending same excel file with different sheet

fdenis — Mon, 17 Sep 2018 15:30:01 GMT

did you check all row1 date dataType?
did you syncronise datatype on tOutputExcelFile2?
can you click and desactivate all unused components to validate error location.
can you click on code tab to check java error? (red underlined)