package openxlsx; function read.xlsx does not work on some excel files (there is no problem with read.xlsx from xlsx package)

[nshoang]

Could you please fix the read.xlsx function in the package openxlsx. The function cannot read Excel files who names end with a number. Thanks!

[nshoang]

I made two identical excel files example.xlsx and example1.xlsx. The function can read 'example.xlsx' but it cannot read example1.xlsx. The reported error is 'Error in eval(expr, envir, enclos) : expecting a single value'.

[shrektan]

I strongly doubt it happens due to this, because I have used this package extensively and never met issues as you report. Please try the dev version. If it still happens please paste your session info here.

[nshoang]

Thanks for your suggestion. I installed the package openxlsx using the command 'install.packages("openxlsx")' (Rstudio for MAC). Maybe the package I have is not the latest one.

[nshoang]

Does any know a possible cause for the error 'Error in eval(expr, envir, enclos) : expecting a single value' with read.xlsx? Thanks!

[shrektan]

I just tried on my Mac. It works fine. If you don't provide a mini example or your sessionInfo(), I won't expect anyone can help you.

[shrektan]

Here's mine:

> sessionInfo()
R version 3.2.2 (2015-08-14)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: OS X 10.11.2 (El Capitan)

locale:
[1] zh_CN.UTF-8/zh_CN.UTF-8/zh_CN.UTF-8/C/zh_CN.UTF-8/zh_CN.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

loaded via a namespace (and not attached):
[1] tools_3.2.2    Rcpp_0.12.2    openxlsx_3.0.0

I tried the version on CRAN, i.e., install.packages("openxlsx"), it works good.

[shrektan]

Another suggestion, if you're eager to solve the problem, you may try the package readxl written by Hadley Wickham.

[nshoang]

Thank you. I will use xlsx or XLconnect package. I have no problem with these packages. I wanted to do conditional formatting with openxlsx but its not worth spending to much time try to understand why read.xlsx does not work with several of my data files (they are big data files).

[awalker89]

Can you please email an example xlsx file to [email protected]. Without a reproducible error there is very little I can do. Thanks.

[gwd999]

Same issue here - using the relative paths of a character vector ChrVec[1:2] with two elements I get "Error: expecting a single value" when adressing the elements ChrVec[1] or ChrVec[2] as input into function "read.xlsx(xlsxFile=ChrVec[1], detectDates = TRUE)" . Strangest thing is that this behaviour is not 100% reproducible, ie. sometimes the function does work, most of the times the error occurrs. sessionInfo() R version 3.2.3 (2015-12-10) Platform: i386-w64-mingw32/i386 (32-bit) Running under: Windows 7 x64 (build 7601) Service Pack 1

locale: [1] LC_COLLATE=German_Austria.1252 LC_CTYPE=German_Austria.1252 LC_MONETARY=German_Austria.1252 [4] LC_NUMERIC=C LC_TIME=German_Austria.1252

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] openxlsx_3.0.0

loaded via a namespace (and not attached): [1] rsconnect_0.4.1.4 tools_3.2.3 Rcpp_0.12.3

UPDATE: The Error is avoided if I JUST open the respective Excel-files once, do a minor change (e.g. change the column widths) and "Save" (and close Excel) - suddenly the read.xlsx function no longer throws "expecting a single value" ERROR. Fascinating ...

[ProfFancyPants]

I had the same issues. It occurred when I was trying to source in a publicly disseminated .xlsx file that can be found here: event.xlsx

> read.xlsx(xlsxFile = file.path(file_dir, event_file), colNames = TRUE)
Error: expecting a single value

From what I can tell the problem has nothing to do with the files name, but the file protection. When I opened the file and re-saved it as a different name ("events2.xlsx") it cleared up the issue immediately. I think the cause has to do with some sort of permission or write protection, because when I initially tried to save the file as the same name, excel kicked out this error:

Specifically, it says it can't be saved due to a "sharing violation". Not sure what all that means, but excel has a fit afterwards.

[nerdcommander]

@ProfFancyPants thanks for the tip, totally saved me, i was having the same troubles, used save as to rename the file, worked as advertised! BTW, I got no errors when using save as to get a new file. thanks!

[lassefolkersen]

@ProfFancyPants thanks, I had the problem too and this load-save tip helped me as well

[melissakey]

I'm having the same issue, but my experience might be helpful in figuring out the problem. I need to open an excel spreadsheet (downloaded from a website) without having text that looks like dates be converted into dates. (In this data set, "SEPT10" is the name of a gene, not September 10th.). The entire data set is extremely large, but I've included a subset which exhibits the problem and contains several proteins whose gene names look like dates. I've tried opening the entire dataset on both my Mac and PC with no luck, and the small version on my PC.

Apparently I will be able to solve my date issue with OpenOffice - but I hope this file helps solve the problem with opening some excel files.

(I should add that the "fix" mentioned above will not work in this particular case. Opening the file in excel would cause the conversion I'm trying to prevent.)

uniprot.data <- read.xlsx("uniprot-proteome_small-test.xlsx") Error in eval(expr, envir, enclos) : expecting a single value

sessionInfo("openxlsx") R version 3.3.2 (2016-10-31) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows >= 8 x64 (build 9200)

locale: [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252 LC_NUMERIC=C
[5] LC_TIME=English_United States.1252

attached base packages: character(0)

other attached packages: [1] openxlsx_3.0.0

loaded via a namespace (and not attached): [1] Rcpp_0.12.7 BiocGenerics_0.16.1 splines_3.3.2 drc_3.0-1 MASS_7.3-45 grDevices_3.3.2 munsell_0.4.3
[8] colorspace_1.3-0 lattice_0.20-34 multcomp_1.4-6 minqa_1.2.4 plyr_1.8.4 car_2.1-3 tools_3.3.2
[15] utils_3.3.2 XLConnect_0.2-12 nnet_7.3-12 parallel_3.3.2 pbkrtest_0.4-6 grid_3.3.2 Biobase_2.30.0
[22] nlme_3.1-128 mgcv_1.8-16 quantreg_5.29 plotrix_3.6-3 TH.data_1.0-7 XLConnectJars_0.2-12 stats_3.3.2
[29] datasets_3.3.2 MatrixModels_0.4-1 gtools_3.5.0 survival_2.40-1 lme4_1.1-12 base_3.3.2 Matrix_1.2-7.1
[36] rJava_0.9-8 nloptr_1.0.4 codetools_0.2-15 graphics_3.3.2 sandwich_2.3-4 scales_0.4.1 methods_3.3.2
[43] SparseM_1.74 mvtnorm_1.0-5 zoo_1.7-13

uniprot-proteome_small-test.xlsx

[Murat-on-github]

I had the same problem. In my case the issue causing it was (.xlsx) extension. I saved file in Excel 98-2003 format (.xls) and this resolved it.