ERROR Read Excel Sheet Names (XLS) 0:231 Execute failed: GC overhead limit exceeded
I am receiving this error. I have roughly 30 excel workbooks with multiple sheets. All I am looking for is a tab called “Confirmation”. If it exists to read it and if not ignore and move on to the next file URI in the list.
Is there a way I can loop through the excel files and only read if they have a confirmation tab if not, ignore and continue. Some try catch block logic to be placed.
You could read the names of the excel sheets and then use a rule and an if switch to decide to use the sheet or not.
https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_if_switch
The challenge is faced is using the read excel sheets name node, I can’t loop through all the files with out getting the error discussed before. This is just to get the sheet name, at this time I am not even reading the contents of file, just the sheet name.
Hi @shubhamss
I hope I understood your question well. Take a look at this wf confirmation_sheet.knwf (30.1 KB). I have the input here: input.zip (17.6 KB) 3-xlsx sheets.
You could try and use the R package readxl to check the sheets and then only use the KNIME import when your sheet is there. Not sure if this would avoid the error message. You could give it a try.
kn_example_r_excel_multiple_sheets.knar (154.5 KB)
Can’t establish that the input is either xls or xlsx.I have some files that are xlsx and some are xlsm
all the more reason to try R. You can just list all files and then keep only those with the relevant extensions. As it happens we have discussed several ways to import old, binary and macro-ridden Excel files over the months, so all hope is not lost but it will need some work.
If you could provide us with an example represeting your various challenges it might help.
https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_xlsb
https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_old_files_import
https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_macros_xlsm
So far the only way that seems to work is if I were to utilize a Tika Parser, as I looking for a specific line in the confirmation tab. But it’s performance is questionable.
I would assume that once you have identified the relevant sheet and imported it successfully you might be able to indentify the relevant cell/line.
Yeah thats what I am doing at the moment, indexOf(“String I am looking for”)
However with R i am getting this Error:
Evaluation error: zip file ‘X:\Comm-FinTech\Comm Consolidated\09. Month End Processes\2011\10-2019 10 Oct\AP Entries\Work Order Accruals\Loader\31-SNSP191081 **–**Work Order Accrual Oct 2019-SNB OU.xlsm’ cannot be opened.
Error: object ‘v_sheets’ not found
I dont know where Euro the bold symbol is coming for?
I would use KNIME’s list file node and the a KNIME Rule engine to only keep the Files with the Confirmation sheets - you could also do that in R but maybe with all the transformations it is not so good.
Also: you will have to use a different tool to open Excel files with macros in them. You maybe will have to differentiate. It will be some work but I think KNIME and R can do it.
About the strange symbols you might want to check if they are there on your file system and if you use UTF-8 in KNIME or if you have to use another encoding.
edit:
–
is and underscore or a long underscore
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.
