Read all sheets from an XLS file in a loop

KCH Workflows
1

This workflow demonstrates how to read all sheets from an XML file using the XML Reader node together with the Read XLS Sheet Names node in a loop.

This is a companion discussion topic for the original entry at https://kni.me/w/a2XQGQ7J9u0N6IZt
2

Hello,

When i use large excel file, am facing the issue like ‘GC overhead limit exceed’, could you please give solution?

3

Hi @baljicbe,

you could try to increase the amount of memory Java is allowed to use. To do so, you need to increase the Xmx value in the knime.ini. See here (under How can I increase the Java Heap Space for KNIME?) for instructions.

Cheers,
David

4

Thanks

5

Hello,

am using R script in R Source(Table), when i execute it shows the following error
importBufferedDataTable(): Supporting only ‘data.frame’, ‘data.table’, ‘matrix’ and ‘list’ for type of “knime.out” (was ‘tbl_df’).

any solution?

regards
Balaji G

6

Hi @baljicbe,

unfortunately I’m not very ‘fluent’ in R, however that error indicates that the object you’re assigning to knime_out hast the wrong type. Could you check the type and make sure it is one of the types mentioned in the error message?
If that doesn’t solve the problem, could you maybe upload a workflow showing the error?

Cheers,
David

7

Hi

Thanks, i got the answer from other peer

8

Hi, is there a way to skip an empty sheet ?

9

Hi @nd1992 -

Have you tried incorporating an Empty Table Switch?

10

A post was split to a new topic: Empty Table Switch Inside Loop

11

A post was split to a new topic: Writing Excel Sheets to Database in a Loop

12

Is there a way to collect all the data from all the sheets by concatenating the rows instead of appending rows?

13

Hello,
I couldn’t make this work on KNIME 4.4. The old nodes are working fine but new ones just duplicates the first sheet with each iteration. Does anyone else have the same issue?

Thanks,
Nezir

14

It’s likely that you if you are replacing nodes (old → new) you need to make sure that the flow variables that determine which sheet to write to are being applied. If you like, you can upload your version of the workflow so that someone can take a look.

It might be best to do that on a separate topic in the main Analytics Platform forum, though.

2 Likes
15

I found the answer.
In the Excel node; for the “Sheet selection” section, “Select sheet with name” should be selected and for the flow variable, sheet name variable should be selected under settings > sheet name.
I did the variable part but selected some other option under “Sheet selection” since I thought the flow variable would overwrite the sheet name. But it seems you also need to choose “Select sheet with name” for that to work with a loop.

N

1 Like
16

An updated version of this workflow:

1 Like