Excel Reader dumps formulas

Greetings all -

Question
A quick question about a possibly obvious matter:

  • The Excel Reader doesn’t import formulas, only the “results” - is this right?

Background:
I’ve some “COUNTIF” functions that need to run, after Knime, for column summaries that I can’t achieve in Knime. As such, I’m trying to import the pre-prepared columns of Excel formulas so that when the output xlsx file is viewed, they match the relevant columns that Knime has so perfectly created for me.

  • Example of pre-prep’d (summarising) formula: =IF(COUNTIF(CP2:CT2,“Non-Compliant”), “Non-Compliant”, “Compliant”)

When however, I import the “source” spreadsheet bearing the 4 columns of source formulas, I only see the default value (“Compliant”), not the formula.

In essence…
I want the formulas to pass through Knime untouched, so they can operate once the resulting xlsx file is viewed in Excel. Is what I’m seeing “just the way it is” or am I doing something obviously foolish? -or is there a solution?

Respectful thanks,
Cardinal

The KNIME import and Export function cannot deal with Excel functions themselves. You can only get the results.

If you want to preserve or restore Excel functions you have to either:

  • use them on a sheet you would not touch with KNIME, Formulas that might refer to a ‘simple’ data sheet you would im- and export to and from KNIME
  • use a Python package like openpyxl or R readxl

Python Excel manipulation

Python Excel table object

3 Likes

mlauber71 Thank you - I figured this was the case but wanted to make sure this fairly straightforward matter didn’t have some cunning resolution.

Appreciate your speed and for kindly taking the time to pull those examples together - I hadn’t thought of that line of investigation so hadn’t yet seen them on the forum. I will look into them and consider the “load versus effort” in relation to their use.

Greatly appreciated. Thank you.

2 Likes

If you look for some formatting to apply to your Excel files from within KNIME you might take a look at the brand new

Continental Nodes for KNIME — XLS Formatter Nodes

https://www.knime.com/community/continental-nodes-for-knime-xls-formatter

Maybe one day they will also come up with something to manipulate the structure of Excel files …

2 Likes

(Ha! - As it happens, once Knime spits out a perfect workbook, I then have to spend time “conditional formatting” and otherwise colourising the data for easier human consumption - this Formatter Node is actually something I was looking for months ago! - Great to see this)

Really REALLY appreciate this news and you going the “extra mile” here in sharing it.

Sincere best regards
Gareth

3 Likes

I FOUND THE SOLUTION

On the Source XLSX file, that contains the formula laden columns you want inserted…

…take the “=” sign off the start of the formula ;0)

Therefore the formula “=IF(…” just becomes “IF(…” text.

I can then, after Knime has completed, perform a find and replace on IF swapping all for =IF.

POW!

3 Likes

Hi @mlauber71 I have Anaconda installed and my Excel formatting Python script is running perfectly. I have installed Python in KNIME Preferences correctly. Everything looks right, but KNIME does not recognize openpyxl as a module. KNIME works with pandas module fine. I appreciate a lot if you can help me how I can import openpyxl in KNIME? THis is the error message I got when I ran “kn_python_excel_manipulate” flow above. Thank you very much.

Traceback (most recent call last):
File “C:\Program Files\KNIME\plugins\org.knime.python2_3.7.1.v201901281201\py\PythonKernelBase.py”, line 278, in execute
exec(source_code, self._exec_env, self._exec_env)
File “”, line 6, in
ModuleNotFoundError: No module named ‘openpyxl’

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.