Urgent! ERROR in reading XLSX file from SFTP location via Java Snippet

[prashant7526]

Urgent help needed:
I am trying to connect SFTP through Java Snippet (for some security reason, I cannot use SSH Node. In my case, SSH node doesn’t work) in KNIME to read the content of the XLSX file (Connection working fine. Problem is in reading XLSX).

I tested the code in Eclipse, and it runs perfectly fine (the dependencies I used are attached below).

Now, when I implemented this code in the KNIME, I got:

WARN Java Snippet 3:22 Evaluation of java snippet failed for row “Row0”.The exception is caused by line 102 of the snippet. Exception message (NoSuchMethodError): 'org.apache.commons.io.output.UnsynchronizedByteArrayOutputStream$Builder org.apache.commons.io.output.UnsynchronizedByteArrayOutputStream.builder()’

While I used the same libraries here:

The problem is that it says the method is not found, while that commons-io jar has the method.

I know this because the Eclipse project gave me the same issue, which worked when I changed the version of the commons-io. Now, why is KNIME giving me this issue? And how to solve this.

Thanks in Advance.

[takbb]

Hi @prashant7526 , could you upload a minimal java snippet that demonstrates the problem, as I think you are much more likely to get assistance if we can see the actual code that is failing.

From experience, debugging this kind of issue even when I have my own code visible in front of me can be challenging, but to do it without being able to see the java call that is causing the error is almost impossible. thanks.

[prashant7526]

Hi @takbb , Sure, below is that Java Snippet:

// Read XLSX file content without downloading
InputStream inputStream = channel.get(out_Path);
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator();

 List<String[]> data = new ArrayList<>();
   while (rowIterator.hasNext()) {
       org.apache.poi.ss.usermodel.Row row = rowIterator.next();
       Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();
       List<String> rowData = new ArrayList<>();
       while (cellIterator.hasNext()) {
           org.apache.poi.ss.usermodel.Cell cell = cellIterator.next();
           rowData.add(cell.toString());
       }
       data.add(rowData.toArray(new String[0]));
   }
   out_Content = data.stream().flatMap(Arrays::stream).toArray(String[]::new);

Make sure you type the below line as it is so the Rows and Cell classes won’t conflict with the import org.knime.base.node.jsnippet.expression.Cell

Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator();
org.apache.poi.ss.usermodel.Row row = rowIterator.next();
Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();

The issue will come in below line:

XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

[takbb]

thanks @prashant7526 , ah yes the fully qualified “Cell” issue - I know it well from playing with poi before!

What imports do you have?

[prashant7526]

@takbb Below are my imports:

// Your custom imports:
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

[takbb]

Also, which version of KNIME are you using?

[prashant7526]

Hi @takbb I am using 5.2.1.

[takbb]

This isn’t a solution to your specific problem but I’m wondering if it can give us any pointers. I have a component on the hub that I have just updated to work with KNIME 5.2

XLSX Sheet Duplicator - 5.2 – takbb

It’s purpose is to read an xlsx file, and duplicate a specified sheet, writing it out to a new file with a comma delimited list of sheet names.

I was wondering if it could be adapted to read your xlsx file. (i.e. unlink the component, open it and take a look at the Java Edit Variable being used for the java calls)

It also uses

import org.apache.poi.xssf.usermodel.*;

and the following XSSFWorkbook call …

        try (FileInputStream inputStream = new FileInputStream(inputFilePath);
             XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
           ...
           ...

which I think you’d need to replace with your call:

       try ( InputStream inputStream = channel.get(out_Path);
               XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
           ...
           ...

but what is interesting is it doesn’t include apache commons-io, but instead uses the following KNIME bundles:

This may (or may not!) be helpful in working out what is going on.

[prashant7526]

Hi @takbb, thanks for directing me to these POI. I didn’t know that these libraries were already there.

I removed my external libraries and added all these below (I know it looks stupid; I added all the versions. I will start cleaning it), and it works like a charm.

WOW! I am learning from my experience as of now: If coding Java, try to use libraries that come with KNIME before adding external jars (it might conflict with the built-in libraries). I think this is what happened in my case.

Thank you so much.

[takbb]

That’s great @prashant7526 , the one problem I found so far with using the specific “additional bundles”, is that when I wrote that above component for KNIME 4.7, it then failed today when I tried it with 5.2 (hence the new 5.2 version!). This was because the v4.7 bundle it was looking for no longer existed.

I’m not sure at the moment what the best way to deal with that is. It would be nice if they had some kind of “logical name” or a level of abstraction to allow it to pick up newer versions automatically, but I guess I will have to leave something for “future-me” to enjoy fixing

Anyway glad to hear you got it working

[prashant7526]

True, normally, the latest version does carry the classes, interfaces, and methods; sometimes, they deprecate as well. You have a point: I don’t want my nodes (if I create one) or the Java Snippet to fail in a newer version and someone has to write it again. KNIME should keep the older version abstract or something.

[takbb]

Yes exactly that. Just to show what happened in my case, this was the original version of the component when it was loaded into KNIME 5.2:

and I had to go manually update the component and change the bundles to use the newer org.knime.ext.poi library as follows:

So in this case it wasn’t a big change to make it work, but even so it’s a component I’ve made public that will just suddenly stop working because the user has updated their KNIME, so that could be problematic if somebody relies on it!

[prashant7526]

Hi @takbb,

I see what you mean now. This is real pain when the node is public and other people use it. I hope KNIME would think or maybe working towards it.

[takbb]

In theory the problem is limited to major version changes, although I’ve not had a chance to test this myself as yet…

Do additional bundles in Java Snippet break when version is updated?

Hi @dnaki, The node will warn you if the versions of bundles change or are no longer available. In the latter case it will fail, in the first it will try to update if possible. If only the minor version of the bundle changed (e.g. update from 2.6.0 to 2.7.0), it will automatically use the updated version. best, Gabriel

[takbb]

Hi @prashant7526 ,

You may wish to “beta test” my new component

Collect Named Java Bundles – takbb

The idea is that you get your java snippet working first off, and include the additional bundles that you require to make it work.

e.g. this is in 4.7

Once its working, you make a note of the bundle names, but without the version numbering, and you place the above component ahead of the java snippet, and give it the comma separated list of bundle names:

You then configure the flow variables for the java snippet to accept the AdditionalJavaBundles flow variable:

At run time, in KNIME 4.7, the component passes the following collection:

In KNIME 5.2, the same config results in this being passed:

Provided that the jar files contain no API differences, or additional jars are needed, the same snippet should work in both KNIME releases even though there is a major version difference between bundles

[prashant7526]

Hi @takbb,

Sure I will and I will definitely will share my testing result. Thanks you for selecting me to do it.