Intialize workbook from file failed: .XLSB Binary Workbooks are not supported

[spremkumar]

Im getting an error while trying to open .xlsb file in talend. Please find attached image for the error message

Is .xlsb file supported in talend?

[Anonymous]

Hello,

The Excel components in Talend are based on Apache POI.

So far, we don't handle passwords in the Excel components, so it's still impossible to read a file protected which can only be accessed with a password, which means

there isn't any component to deal with xlsb files in talend.

Are you able to convert it to the XML based format xlsx (or xlsm in case of macros)?

Best regards

Sabrina

[spremkumar]

Hi Sabrina,

Yes I'm able to convert it to xlsm format and tried to run. It worked.

But right now Im getting "Exception in thread "main" java.lang.OutOfMemoryError: Java heap space". This occurs because it's running a Job that processes a very large amount of data.

Attempt 1: I have also tried to allocate more memory by increasing the JVM parameters by 8GB in the Job Run VM arguments table but it still throws me out of memory error.

Attempt 2: Since its a large volume of data, I used cursor option (5000 rows - 10000 rows) in the tDBinput component to loads and processes the data in batches but still memory issues

How can i resolve this issue??

 

[Prakhar1]

@sharvesh premkumar​ use this JVM parameter

-XX:-UseGCOverheadLimit

[spremkumar]

Hi @prakhar dubey​ Should it be something like below ?

[Prakhar1]

yes please also add two more parameters :

-Xms4096M

-Xmx111536m

Remove the first one you are using

[spremkumar]

@prakhar dubey​ The job runs fine now but the .xls sheet is not getting populated with values. Here as you can see in the screenshot the job runs perfectly fine where it affects the sheet "New BB". However, after the job run when I open the excel file, the values are not populated

[Prakhar1 · Accepted solution]

Hi @sharvesh premkumar​ I have not worked with this component before but I tried to find something on google.

can you try to use tFileExcelWorkbookSave  and activate the Recalculate 

[spremkumar]

@prakhar dubey​ Awesome this worked for me. Thanks Prakhar

Take home message: Always add tfileexcelworkbooksave next to tfileexcelsheetoutput component to save the changes

[Prakhar1]

Glad it worked... And thanks for the note..​