Node "Excel Writer" Error/bug in KNIME 5.3.1

[Wile]

Hello Community,

We use the “Excel Writer” Node to write our results to file system. This file is used to update PowerBI.
With KNIME 5.2.1 no problem.
with KNIME 5.3.1 Power BI reports failure. (Can´t find the right sheet, it has problems with the navigation)
If we open with KNIME 5.3.1. written “result.xlsx” with Excel directly and save it without changes, Power BI loads without failure.
Are there any changes made in the Excel Writer Node with KNIME 5.3.1 ?
Greetings Alex

[mlauber71]

@Wile welcome to the KNIME forum. This sounds like this problem. You might try and remove the properties from the saved excel file:

Excel Writer and Metadata (and PowerBI)

Hi @hotzm: thanks a lot for reopening the ticket (also for the kind mail contact). Yes, “Verfasser”=creator (or author). I also didn’t find any entry regarding “absolute path”. Regards, ReWi

[Wile]

Thank you for your fast reply, I will test it!

[Wile]

tested and: It works!
But it´s not an issue with the author. We adapted the phyton script just to open and close the excel. and now it works:

import knime.scripting.io as knio
import pandas as pd
import openpyxl
import datetime
import os

Retrieve the file path from KNIME flow variables

v_excel_file = knio.flow_variables[“file_path”]
v_data_path = knio.flow_variables[“context.workflow.data-path”]

Load the Excel workbook

fh = openpyxl.load_workbook(v_excel_file)

Save the updated Excel workbook

fh.save(v_excel_file)

from my point of view this should not be. It seems that the original “Excel Writer” does something wrong.

[mlauber71]

@Wile Excel as a way to transfer data is not optimal. In another thread there was the suggestion to use parquet to bring data to PowerBI. Maybe also try this.

Excel sheet name after overwrite with Excel Writer Node

@scazzetta there have been similar reports and there is a ticket. Though not sure if this is your exact problem One suggestion was to use parquet files to transfer data which in my experience is more stable then Excel …

[Wile]

Thank you for your suggestion. We have to evaluate it, because some Excel files are also used by “humans”
But also good to know, that the general issue is already known, so I can looking foward to any KNIME updates

[hotzm]

Hi @Wile,

we recently fixed a bug wrt. Excel Writer PowerBI compatibility in KNIME 5.3.2 (internal reference AP-23021, fixing “File contains corrupted data.” error). Could you try again with this version?

Though, I am a bit worried that it might not be the fix for your problem, since you report that PowerBI has trouble finding sheets/navigating them. We know of a bug in the underlying library (we’re tracking it with AP-22823), where sheet dimensions are not correctly updated to reflect the actual data, and PowerBI seems to be one of the only tools that cares about that, all other tools seem to just read the data that’s there (and it is, just the metadata is not accurate). The workaround is to write the file with the “Evaluate formulas” option checked. This makes it less usable for very large exports (because it has to hold everything in main memory to chase formula references), but updates the metadata correctly.

Best,
Manuel

[Wile]

Hi @hotzm,
sorry for my late reply, I was on vacation.
I tested it with KNIME 5.3.2 and it works!!
Thank you a lot!
And by the way, I learned a lot with parquet