Appending same excel file with different sheet

Six 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]

Twelve 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!

Six 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]

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

Twelve 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!

Six 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 )

Twelve 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!

Six Stars

Re: Appending same excel file with different sheet

still getting error...please check attachment

Twelve 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!

Six Stars

Re: Appending same excel file with different sheet

even after changing datatype aslo getting same error..

Twelve 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!

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

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

Download