KNIME Excel Reader xlsx vs. xls Issue

KNIME Analytics Platform
1

Hello Fellow KNIMErs,

Ok this one blew my mind and still is a bit, but I think I isolated it. So I have a directory variable. Directory is on our network. Let’s call it…

a:\ourdirectory\

…with the variable being…

VAR1 = a:\ourdirectory\

…of course.

Directory contains 2 distinct and unrelated/unattached *.xls files with “Compatibility Mode” appearing at the top of each file when you open them. I need to bring one tab from each of the 2 excel files into my workflow. So I open 2 Excel Reader nodes. No issues right?

Well I configure the first excel reader node to bring in the first xls file table. My configuration consists of…

Select File to Read: Files in Folder; Read From Local File System; Folder: Variable (e.g. the directory above). Filter options: I choose the first file name. No Issues at all.

I go to configure the second excel reader node. Same was as above, but with different file name for the second excel file and…

BAM…my preview at the bottom won’t work…it can’t pull it in.

If I change the order of configuring the nodes, e.g. configure the second node first and vice versa…it’s always the second configuration that won’t work.

If I change the files from xls to xlsx by opening file and then File, Convert, Compatibility Mode and convert it to xlsx, the issue disappears.

So my questions are…

  • Has anyone else run into this issue?

  • Is there a workaround? We don’t want to have to convert our xls files to xlsx every day (we receive them from others and from systems)

Thanks as always.

Chris

2

is it possible to use a loop or an R package?

If it is always the second file it sounds like somehow it is blocked by a scan or something. One would have to try to do this on another system. What system do you use?

1 Like
3

Hi @chrisgranelli,
just few questions for understanding this issue.
a) Is the second xls reader just a copy of the first one or did you use a new one?
b) If it is a copy does the xls files have the same structure?
c) If no. did you press the refresh button for the preview? If the structure is different you normally get a notification in the preview section.

BR

1 Like
4

No it is not a copy nor is the structure the same. The resh button gets the same issue.

5

Having example files would be more helpful but I assume an upload of example files is not possible, right?

So we need more other datailed information.

  • the source system for both files is the same?
  • did the excel files have specific addon data e.g. chemical structures?

What you can also do is to check if the files are realy in a proprietary xls format by opening the files with an text editor.

6

Hello @chrisgranelli,

have you managed to figure it out? If not can you tell us what KNIME version are you using? Why are you using Files in folder options if reading only one file with each Excel Reader? Also does same happen if you place both Excel files locally and not on your network drive?

Br,
Ivan

7

I haven’t solved the issue. Instead I did the following, which works but is less brief…

  • List Files Node - produces a table of the 2 files in the directory

…then I do this twice for each file…

  1. Row Filter Node - I fill in wildcard so one file is chosen
  2. Table Row to Variable Node - Makes file location a variable
  3. Excel Reader Node - I use the location variable to read respective file

This approach uses 7 nodes, as opposed to 2 nodes in the approach I was trying to do in my initial question.

Can’t explain it but I think I’ll move on from this…it works. Going forward I’ll be working towards having all excel files be xlsx or later.

Thanks.

Chris

1 Like
8

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.