putexcel stuck on file

[Sergiy Radyakin]

Dear All,

a syntax error has derailed my normal Excel production leaving the -putexcel- facility in a locked out state so that I can neither create a new file, nor terminate creation of the previous:

. clear all

. putexcel set "C:\temp\delme.xlsx"
Excel file open
    Type putexcel save to save the existing open file. Type

        putexcel describe

    to see current settings.

r(198);

. putexcel describe

  -----------------+-------------------------
  Filename         |  C:/temp/spdoc_LCN.xlsx
  Filetype         |  xlsx
  Write mode       |  modify
  Sheetname        |  Argentina
  Locale           |  UTF-8
  Open file handle |  yes

. putexcel save
file handle not found
r(604);

Stata/SE 17.0 for Windows (64-bit x86-64)
Revision 14 Jun 2021

Is this a known issue?


The following is a minimal code example to reproduce the situation:

clear all
version 17.0
putexcel set "C:\temp\demo.xlsx", open sheet("demo") replace
  putexcel A1="Test"
  putexcel B3=dummy  // intentional error here
putexcel save
// END OF FILE

Thank you, Sergiy

[Leonardo Guizzetti]

I've run into this problem sometimes too, and also with -putdocx-, and the analogous solution there is to use -putdocx clear-. I would have expected that -putexcel clear- should behave similarly but it does not. The behaviour seems to be a bug, either because internally Stata has lost the file handle, or because putexcel clear cannot overcome this problem and reset the file information.

putexcel clear clears the file information set by putexcel set.

. putexcel clear
file handle not found

I ran your example on Stata/MP 17.0 for Windows, revision 19 Jan 2022.

[Sergiy Radyakin]

Dear Leonardo Guizzetti,

thank you very much for replicating the problem in the contemporary Stata version and for the additional information regarding putdocx behavior.

At this time I can only restart Stata to recover, as no command ran from that session seem to have any effect on the stuck Excel file.
I have also found that during development time it makes sense to not use the open option of the putexcel set command. The program is executed muuuch slower, but in case of an error I can simply fix it and rerun, without having to close the whole Stata session.

I hope the developers will address this bug in a future update.

Thank you, Sergiy Radyakin

[Leonardo Guizzetti]

You are welcome. For what it's worth, I don't bother opening excel files in memory and creating those sheets is fairly quick.

[Anna Powell]

FYI for users - I'm having this issue occur with Stata 18 as well. I also get the "file handle not found" and can only resolve by restarting Stata.

Unlike Leonardo, I have to use "open" in order to make putexcel run faster. Not sure why, but without "open" it takes 5-10 minutes per table instead of 5 seconds.

[Leonardo Guizzetti]

The issue is still not resolved in Stata 18.

in answer to why -open- is faster, it’s because Stata opens the Excel file in memory, only saving to disk when you explicitly tell it to. This will always be faster to the default behaviour with putexcel which is to open, modify and save the file with every single operation. The former is useful for tested programs, while the latter is useful for interactive and testing purposes.

[Daniel Schaefer]

Leonardo is correct in #6, but it is interesting that you say you need to wait 5 to 10 minutes per table. Wait times this long should imply that you are working with a large excel file, a spinning disk drive, synchronous rather than asynchronous reads and writes to the file system, or all of the above. Otherwise, I would expect even loading files over the Sata cable (an admittedly series bottleneck) to be on the order of seconds, not minutes.

Also note that every time the excel file is loaded into memory, it must also be decompressed. An excel file is essentially a compressed xml file.

[Leonardo Guizzetti]

I took the time estimates as a bit hyperbolic, but I’ve had dynamic reports that took some minutes to generate when I first used putexcel in interactive mode, not because of any complexity, but more because of the customizations that I would also do in Mata. You are correct though, it could be a latency or data size issue too.