KNIME 4.3.2 Excel writer overwriting problem

Hello,

I have some problem with the new version of the Excel Writer.
I need the node to overwrite the file every run, but after creating the first version of the file it never gets overwritten.

In the Write options of the node I checked “overwrite”, but without solving the problem.

Can you please help me?

Thanks

1 Like

Hi @rsemino,

can you please share the error message or the workflow with us?

Thanks
Kathrin

Hi Kathrin,

I have no error message. The node run as usual but without overwriting the file.
Here the workflow.
Thanks,
Riccardo

KPI ACAM ASM 1.knwf (21.8 KB)

Thank you for sharing the workflow.

After replacing the data it worked fine for me. Could it be that you had the excel file open already? In this case you need to reopen the excel file, after overwriting it.

No, I always work with the Excel file closed.
For the moment I delete the excel file before the execution of the node, but it is a workaround that is not good because I lose the subsequent processing done on the file. Do you have any ideas what the problem may be?

For debugging,
have you tried to uncheck the overwrite and see what happens?

Hi @Daniel_Weikert,

if I check “fail” it doesn’t run because the file exists, if I check “append” and “overwrite” in sheet options the problem still remains.

Hello @rsemino,

can you try this workflow?
KPI ACAM ASM 1_ipazin.knwf (20.4 KB)

It writes two tables to same Output.xlsx file located inside data folder within workflow directory. With just opening Excel Reader you can see if file was successfully overwritten. You can also change output location to test overwriting on your preferred location.

Br,
Ivan

@rsemino ,

is G:// some kind of special drive, e.g., a network drive?

Best,
Mark

We have the same problem in our company when using KNIME.

Overwriting doesn’t work, we need to deleted the file first every time we want to overwrite it.

@Kathrin and others,
I can confirm that the problem exists.

I tested it with this simple workflow:
image

What workflow does - there are two Excel writers, both trying to write data to the same file. Excel Writers have default options except there is “overwrite” selected for “file exists” option.

If there is no file in the location, writing always works fine.
If there is file and it needs to be overwritten, result appears to depend on path. For some paths it works as expected, and for some the file is just not overwritten, and there is no error messages.

Example paths that work fine for xlsx overwrite:
\\companyshare.net\FolderRedirection\myuser\Exports\test.xlsx
C:\Users\myuser\Documents\test.xlsx

Example path that does not work for xlsx overwrite:
\Client\C$\temp\test.xlsx

We are guessing that overwrite for some reason does not like the “$” sign in the file path. :slight_smile: However it is not a problem for new file.
Problem is the same if I pass xlsx file location as a flow variable that is converted to path.
We are running Knime on a Citrix machine as remote app. Basically it runs on a remote virtual machine PC.

1 Like

Can confirm. The “Documents” folder in my case is on a network drive which in the paths includes “HomeDirM$”, eg, also a “$”.

I do get an error from Excel writer, even when writing it the first time successfully. I think the error comes from the config dialog:

Error while updating status message: Unable to access \myserver.com\HomeDirM$\xyz\Documents\test_overwrite.xlsx

Overwriting on this network address never works. I’ve already mentioned in other topics what I think of this new “Paths” system. Eg. it should still be in KNIME labs and not the default.

1 Like

Thank you both @Experimenter and @kienerj your findings are super valuable for us and we will have a look into the $ issue.

@rsemino, @Daniel_Weikert and @matascip do your paths also contain $ signs or some other “unusual” characters? Are you also writing to network drives?

Best,
Mark

3 Likes

I have the exact same issue and the path does not contain any special characters such as $. I believe it has something to do with writing to a network/shared drive.

I was able to successfully overwrite a workbook saved on my desktop, but when I changed the path a shared drive (Google Drive) the overwrite did not work.

Hi @nyarmussa first of all welcome to the community!

I’m still not able to reproduce the problem. Could you or anyone who experiences this problem do me a favor and try the following. Does the overwriting work if you open and close the file in question via the explorer before executing the node?
Steps to be taken:

  1. Reboot your system
  2. Open the workflow and overwrite the file. Expected outcome overwrite fails
  3. Open and close the file to overwrite via your explorer
  4. Execute the workflow again. ← Did it work this time and does it also work when you execute the workflow several times in a row?

Best,
Mark

1 Like

@Mark_Ortmann
Tried the steps 2-4, no difference.

1 Like

Ok thanks for the info!

Hi @Experimenter,

A few questions spring to mind. Apologies if these have been answered already but I didn’t spot it.

  1. Can you confirm that you can open, modify and save the file using exactly the same path name from outside of Knime. It is possible to be granted read/write access to a network share but not modify access, so I want to rule that possibility out.

  2. How are you entering the file path in Excel Writer. Is it directly in the input field, or via a flow variable?

  3. Just one other thing that’s niggling me… can you confirm that the paths start \ (two backslashes) and not single back-slash, as in some of the above posts, one of the backslashes has disappeared. Almost certainly just a copy/paste error in the posts, but I just want to be sure.

  4. Are you seeing any other strangely named .xlsx files appearing in your network share when it tries to write?

e.g. something like this? (In this share, I’ve only been trying to write doc1.xlsx but these other files have appeared, possibly because I’ve been playing around with permissions on the share)

Having had a think about this, I would see that we need to work out using common workflow which has been proven to work using a fileshare, to home in on where the problem is. To that end, I have created one. Please open the attached workflow. I have included in it a component that I created called “Open File or Folder”.

The only config you need to do is in that component. Change ONLY this setting as the rest of the workflow will work from it.

After that, run the workflow.

It writes some dummy data to the xlsx file you entered in the first node:
image

It then appends it repeatedly to the file 20 times in a loop. Each time it collects file meta data stats.

At the end of the process you can view the output ad it should show the file getting larger with each iteration.

Please try running this workflow on your system and see what the results are. This might yield some clues about where to look next.

Test write fro Excel Write to network share.knwf (69.4 KB)

@takbb

  1. The issue seems nothing to do with access rights. Old Excel writer does work with no issues, same paths.
    image
  2. Flow variable. Issue persists if path is direct input.
  3. Yes. Variable:
    image
    or direct:
    image
  4. Now when you ask - yes, indeed, I noticed a temporary (hashed name) xlsx file flickering in folder for a moment. But it disappears immediately, so it is hard to notice.

After this, do I still need to play with your workflow? :slight_smile: