Very new to the Knime platform, but my question is (hopefully) pretty basic:
I have excel based queries which are connected to a SharePoint List. This is list is exported onto excel and then it becomes a refreshable table where I can update as my users update their SharePoint table.
What I would like is to be able to have Knime refresh the queries that sit inside the excel list (preferably on a scheduled basis) and then to output into a Table Reader which I can append to others that are similar.
I have tried to use the REST GET node, with very little success.
TLDR: Knime to refresh my excels for me automatically without having to open excel and refresh them all, or use VBA to refresh all of them at once.
There has been a discussion before about excel formulas. It might be possible to do that via python code. You would have to load the excel file containing the formulas, run the code and save it again.
I have not yet found a function for that in the powerful Python package openpyxl.
You might also want to check out this thread although it would only work to recalculate the formulas prior to loading the data into KNIME. You would not be able to save the excel file with the formulas.
Has anyone found an easy solution to reading refreshed data instead of the previous version? i.e. even if the excel workbook is set to automatically refresh on opening, the reader doesn’t retrieve the latest data.
If the excel workbook is set to automatically refresh upon opening you can use the Bash node to invoke an external shell that opens and closes your Excel file. Then Excel Reader (XLS) will retrieve latest data. In case opening and closing Excel needs a bit of time use Wait… node in between.