Read .xlsb File

Hey,
I was having a problem reading files with .xlsb extension, can KNIME read these kind of files?
Thanks

You can try the Tika Parser node for this, which is available in the KNIME Textprocessing extension. The node will read in the contents of the XLSB file as a single cell, so you then have to do some additional processing to get the data into a proper table format. This will likely involve the String Replacer, Cell Splitter, and Ungroup nodes, among others.

(Unfortunately I don’t believe the usual Excel Reader node will work for this case.)

2 Likes

Is there any example on how to make it work?

The postprocessing that is needed will depend on how your data is formatted. If you have a sample data file you can post, maybe someone can put together a sample workflow for you.

Here’s a partial workflow I came up with (as this question came up again during KNIME Summit). Maybe it will help you.

SplittingLargeStringsIntoRows_Example.knwf (28.6 KB)

4 Likes

Hi Scott, sorry for the intromision, I’m in the same dilemma, I tried the Tika Parser node, but doesn’t work. This can be because the xlsb file has many work sheets?

image

Thanks in advance

It’s hard for me to tell from just a screenshot of the workflow, but I suspect that you’re correct that the Tika Parser is not going to handle multiple sheets in an XLSB file well.

Do you have the option to convert XLSB to XLSX before reading into KNIME? We definitely have example workflows that can let you parse multiple sheets from a standard Excel file.

2 Likes

Well, I can do it, but this reduce the productivity in workflow, because I have to search the information in share point and convert it to xlsx, and then save it in another folder, and this increase the error possibility, so, sure I can do it, but the idea is not :wink:

There is a way with the Python package pyxlsb that allows you to read these special binary excel formats. I have attached an example where the file also includes a small Macro.

kn_example_python_excel_xlsb.knwf (31.0 KB)

5 Likes

Thank you, I will try these nodes and write to you again :wink:

2 Likes

hi @mlauber71 I have installed the Pyxlsb package. however I am still getting this error. could you assist.

Very new to python. What is a good way to learn more?

Hi @rparr009 , as per the message, it looks like pyxlsb is not properly installed. Can you explain how you “installed the Pyxlsb package”?

2 Likes

@rparr009 glad you asked.

I have a collection about KNIME and python

You might want to start with the official guide

https://docs.knime.com/latest/python_installation_guide/index.html

If you have familiarized yourself with the concept you could use the most important commands to manage your python environment and install packages also via command line.

The tool in question (pyxlsb) is also on anaconda (conda-forge channel) so this might be another way to manage dependencies which are always a thing with python:

https://anaconda.org/conda-forge/pyxlsb

3 Likes

Hello everyone,
with version 4.4 of the KNIME Analytics Platform which will be released in summer the Excel Reader will support reading xlsb files. If you want to try it out before have a look at the nightly build.
Bye
Tobias

8 Likes

Hi all,

4.4 is out and ready to be used. Thanks for all the feedback and let us know if you have any other feature requests!

Best,
Simon

3 Likes