When reading an Excel document using readxl::read_excel, a blank line is output to the console. This is not usual behaviour for other functions (such as read.table). This line would appear to not be useful and can disrupt console output when used in a loop (for example when using a progress bar).
Is there a way to suppress this behaviour?
suppressMessages does not work. I can use capture.output and then read the resulting string in with a textConnection, but this introduces new issues and I am certain there is a better solution.
result = readxl::read_excel(readxl::readxl_example("clippy.xls"))
I don't seem to experience this behavior. Could you share the output of the console and the code used. And maybe an example how it'd affect a loop / progress bar.
Below is some code that shows the disruption to a progress bar. I am running this in RStudio on a MacBook. The behaviour is similar in R run from the Terminal (although the progress bar does not flash, but is simply not shown).
I am not sure how best to post the console output, but the only output from the code below is the finished progress bar for the second loop and nothing for the first. The key issue as far as I can see is the additional line being output by readxl and overwriting the progress bar every cycle - any code that does not output this line plays nicely with the progress bars. The output of the code from my first post is merely a blank line and not really postable!
# Reading in the Excel file causes the progress bar to flash (it is being overwritten by the rogue console output from readxl)
pbapply::pboptions(txt.width = 50, style = 3)
pb = pbapply::startpb(min = 0, max = 20)
output = lapply(1:20, function(i){
pbapply::setpb(pb, i)
result = readxl::read_excel(readxl::readxl_example("clippy.xls"))
Sys.sleep(0.1)
})
# With the read_excel line commented out, the progress bar flows as expected.
pbapply::pboptions(txt.width = 50, style = 3)
pb = pbapply::startpb(min = 0, max = 20)
output = lapply(1:20, function(i){
pbapply::setpb(pb, i)
# result = readxl::read_excel(readxl::readxl_example("clippy.xls"))
Sys.sleep(0.1)
})
Here is my console output (just pasted as preformatted text).
Note the missing elapsed progress bar after the first loop. Note also the empty line before the cursor prompt in the final line where I just call read_excel.
You appear not to be experiencing my issue which makes me wonder if it is a peculiarity of my system. I will try this later on different systems (only have the laptop to hand right now) and run some tests.
> # Reading in the Excel file causes the progress bar to flash (it is being overwritten by the rogue console output from readxl)
> pbapply::pboptions(txt.width = 50, style = 3)
> pb = pbapply::startpb(min = 0, max = 20)
| | 0 % ~calculating > output = lapply(1:20, function(i){
+ pbapply::setpb(pb, i)
+ result = readxl::read_excel(readxl::readxl_example("clippy.xls"))
+ Sys.sleep(0.1)
+ })
>
> # With the read_excel line commented out, the progress bar flows as expected.
> pbapply::pboptions(txt.width = 50, style = 3)
> pb = pbapply::startpb(min = 0, max = 20)
| | 0 % ~calculating > output = lapply(1:20, function(i){
+ pbapply::setpb(pb, i)
+ # result = readxl::read_excel(readxl::readxl_example("clippy.xls"))
+ Sys.sleep(0.1)
+ })
|++++++++++++++++++++++++++++++++++++++++++++++++++| 100% elapsed = 02s>
> result = readxl::read_excel(readxl::readxl_example("clippy.xls"))
>
It might be a mac vs windows thing. Do you also have the issue if you use another package like xlsx? If that would work, it might be due to the package...
This only occurs with readxl. I have tried with openxlsx and that works great (only for xlsx though). I wonder what in that package could cause this though? I suspect it is a side effect of the warning messages, but I have not managed to dig far enough into the (quite complicated) readxl code to track this down.
I was rather hoping there would be a workaround - a magic 'suppress_all_console_output()' command that would force a fix!
Update:
I have just tried the code on a Windows machine and the problem does not occur.
On my MacBook, a --vanilla R session does not fix the problem.
On a Linux machine via the Terminal, the problem DOES occur.
On a Linux machine via RStudio Server the problem DOES occur.
