Appending same excel file with different sheet

Five Stars

Appending same excel file with different sheet

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]

Eleven Stars

Re: Appending same excel file with different sheet

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,

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: Appending same excel file with different sheet

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]

Five Stars

Re: Appending same excel file with different sheet

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

Eleven Stars

Re: Appending same excel file with different sheet

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: Appending same excel file with different sheet

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 )

Eleven Stars

Re: Appending same excel file with different sheet

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.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: Appending same excel file with different sheet

still getting error...please check attachment

Eleven Stars

Re: Appending same excel file with different sheet

set Date type to String.
it will work

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: Appending same excel file with different sheet

even after changing datatype aslo getting same error..

Eleven Stars

Re: Appending same excel file with different sheet

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)

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.