Readin .xlsx file into R

Question

Im trying to read an excel file into R. It's about the following file in my cwd:

 > list.files()
 [1] "Keuren_Op_Afspraak.xlsx"

I installed XLConnect and am doing the following:

library(XLConnect)
demoExcelFile <- system.file("Keuren_Op_Afspraak.xlsx", package = "XLConnect")
wb <- loadWorkbook(demoExcelFile)

But this gives me the error:

Error: FileNotFoundException (Java): File '' could not be found - you may specify to automatically create the file if not existing.

But I dont understand where this is coming from. Any thoughts?

possible duplicate of [Importing .xlsx file into R](http://stackoverflow.com/questions/7049272/importing-xlsx-file-into-r) — , Jul 08 '15 at 08:30

score 5 · Answer 1 · answered Jul 08 '15 at 08:43

5

I prefer using the readxl package. It is written in C so it is faster. It also seems to handle large files better. The command would be:

library(readxl)
wb <- read_excel("Keuren_Op_Afspraak.xlsx")

answered Jul 08 '15 at 08:43

Steve Rowe

19,411
9
51
82

score 3 · Answer 2 · edited Mar 03 '20 at 14:46

3

You can also use the xlsx package.

library(xlsx)
wb <- read.xlsx("Keuren_Op_Afspraak.xlsx", sheet = 1)

Edit :@Verena

You can also use this function much faster:

wb <- read.xlsx2("Keuren_Op_Afspraak.xlsx", sheet = 1)

edited Mar 03 '20 at 14:46

Leonhardt Guass

773
7
24

answered Jul 08 '15 at 08:56

Mostafa90

1,674
1
21
39

1

From this package I would use read.xlsx2("Keuren_Op_Afspraak.xlsx", sheet=1) it is a lot faster. – Verena Haunschmid Jul 08 '15 at 10:33

score 2 · Answer 3 · answered Jul 08 '15 at 08:35

2

You have to change your code that way:

library(XLConnect)
demoExcelFile <- "Keuren_Op_Afspraak.xlsx"
wb <- loadWorkbook(demoExcelFile)

You probably took the example from here: http://www.inside-r.org/packages/cran/XLConnect/docs/loadWorkbook

This line

system.file("demoFiles/mtcars.xlsx", package = "XLConnect")

is a way to get sample files that are part of a package. If you download the zip File of XLConnect and look into the folder structure you will see that there is a folder demoFiles that contains mtcars.xlsx. And the parameter package="XLConnect" tells the method to look for the file in this package.

If you type it into the command line it returns the absolute path to the file:

"C:/Users/Expecto/Documents/R/win-library/3.1/XLConnect/demoFiles/mtcars.xlsx"

To use loadWorkbook you simply need to pass the relative or absolute filepath.

answered Jul 08 '15 at 08:35

Verena Haunschmid

1,252
15
40

@ExpectoPartonum, thanks. I already tried this but then I get a memory error: Error: OutOfMemoryError (Java): Java heap space. Any thoughts on this? – Marc van der Peet Jul 08 '15 at 09:01
How large is your file? – Verena Haunschmid Jul 08 '15 at 10:30
@MarcvanderPeet maybe you need to increase your heap: options(java.parameters = "-Xmx1000m") (insert any number that suits you but of course you have to take care how much memory your machine has) – Verena Haunschmid Jul 08 '15 at 10:32
@MarcvanderPeet You could also try to load each sheet separately (if you have more than one) – Verena Haunschmid Jul 08 '15 at 10:33

Readin .xlsx file into R

3 Answers3