Reading a folder of XLS files with different column numbers

KNIME Analytics Platform
1

I'm trying to concatenate many .xls files in a folder into one table.

I've tried using List Files -> Table Row to Variable Loop Start -> XLS Reader -> Loop End (and assigning XLS_LOCATION in the 'Flow Variables' tab of the XLS Reader to 'Location') but I get an error saying:

ERROR Loop End: Execute failed: Input table's structure differs from reference (first iteration) table: different column counts 28 vs 32.

How could I solve this problem? 

2

Hi,

I believe if you have a "template" table selected as the source in the xls reader, it will use this as a guide for the column structure for the files read in using the flow variable.  As long as the template contains all possible columns (and the headers and column types match) it should work.  I have had to do this, but I think it was with the csv reader?

Alternatively, you may be able to have your loop read the template file first (you may need a row with dummy data), delete the data later.

Hope this helps.

3

Hi,

you need to activate the Option "Allow Changing table specifications" in the Loop End node dialog and maybe the option "Allow variable Column types" as well.

Best regards, Iris

 

Allow variable column types
If checked, the loop does not fail when the column types between different table iterations change.
Allow changing table specifications
If checked, the table specifications between iterations can differ. If column have been added or removed between iterations, missing values are inserted accordingly in the result table. If not checked and the table specifications differ, the node will fail.
6 Likes
4

Hello @Iris .

I have the same situation but with the node “Variable Loop End”. The error is as follows: “ERROR KNIME-Worker-1-Variable Condition Loop End 0:92 Node Execute failed: Input table’s structure differs from reference (first iteration) table: different column counts 22 vs. 1”. How can I configure the options that you say in this node?
Thanks for your attention

Hello @sebversailles
How did you solve the situation? I have the same problem with the node “variable loop end”

5

Hello @Hugsanch,

this is not possible in Variable Condition Loop End node. Have you tried implementing a workaround mentioned here?

Will add +1 on ticket mentioned in linked topic.

Br,
Ivan

1 Like
6

Hello @ipazin and thanks for responding.

I have read the solution you propose but I do not understand it, could you please explain in more detail and for someone new (with an example flow if possible).
Thanks for understanding.

7

@Hugsanch maybe you could check this example if you could adapt it.

Here are some more options about excel import to explore

Another idea could be you use one of the examples and make a setting that does represent your full challenge.

1 Like
8

Hello @Hugsanch,

here is workflow example you can check:

The example itself doesn’t make much sense but hopefully you will be able to figure out how to set up your flow.

Br,
Ivan

1 Like