topic Intialize workbook from file failed: .XLSB Binary Workbooks are not supported in Talend Studio

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

spremkumar — Sat, 16 Nov 2024 00:40:27 GMT

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?

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

Anonymous — Mon, 08 Feb 2021 06:25:52 GMT

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

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

spremkumar — Mon, 08 Feb 2021 12:37:35 GMT

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

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

Prakhar1 — Mon, 08 Feb 2021 14:04:35 GMT

@sharvesh premkumar use this JVM parameter

-XX:-UseGCOverheadLimit

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

spremkumar — Wed, 10 Feb 2021 08:47:33 GMT

Hi @prakhar dubey Should it be something like below ?

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

Prakhar1 — Wed, 10 Feb 2021 09:05:44 GMT

yes please also add two more parameters :

-Xms4096M

-Xmx111536m

Remove the first one you are using

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

spremkumar — Wed, 10 Feb 2021 09:29:45 GMT

@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

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

Prakhar1 — Wed, 10 Feb 2021 11:56:10 GMT

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

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

spremkumar — Wed, 10 Feb 2021 14:37:38 GMT

@prakhar dubey Awesome this worked for me. Thanks Prakhar

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

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

Prakhar1 — Wed, 10 Feb 2021 19:03:16 GMT

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