Read multiple Excel sheets

KNIME Analytics Platform
1

I often have the task of processing several Excel sheets with the same data structure.
I've tried the following :

List Files --> TableRow To Variable Loop Start .---. XLS Reader

however unfortunately the XLS reader does not show any Flow Variable .

Could you please help ?

2

After a while I get Flow Variables, with also XLS_LOCATION, however when I set it to URL I get the notification "Invalid settings: Please select a file to read from."...

3

Please try out this post, it should apply to XLS Reader too.

 

http://tech.knime.org/forum/knime-developers/use-flow-variable-as-file-reference-in-sd-reader-node

Simon.

4

Sorry if this is thread hijacking.

I have a similar challenge, but in our case it is one workbook with a lot of sheets.
I do not know in advance how the sheets are named, but would still like to loop over each one.

5

I have the same problem, how can I find a list of worksheet in each excel file? and loop over them?

Wei

6

If you know the sheets to read from a single XLS file, you can use the Table Creator to have the sheet names created and then use the TableRow To Variable Loop Start to read one sheets at a time. You need to configure the XLS Reader with the variable input from the loop start and select on valid sheet, switch to the Flow Variables tab and select the variable name that contains the sheet names. Make sure you close your workflow loop with the corresponding Loop End node. That's it.

7

I have similar requirement. But I don't think Table Creator is an acceptable solution for our users. Is there any way to read the name list of excel sheets?

8

Maybe we can call a vbs by "External Tool" to list all sheets and save it to a text file to process. I will try this approach.

But I do not know how to deploy the scripts with the workflow. :(

9

The List Files node might help to get all XLS files contained a folder?

10

No. I means all sheets in an xls file. Not all xls files in a folder.

11

Hi - I have exactly this same problem (using v2.8 nightly), but there is no "0" option in my Flow Variable sheet. Is there another workaround?

 

thanks,

JP

12

There is a new node called Read XLS Sheet Names which should do weha tyo uneed. It is available as of KNIME 2.8, I believe.

1 Like