Error message using read_excel "Error: std::bad_alloc"

Question

I'm trying to open an Excel file with the read_excel() function from the readxl package. But I don't know how to specify the path to the Excel file.

When I enter the file path by following the steps given in the answer, I receive the error:

"Error: std::bad_alloc".

Don't put anything in `file.choose()`. It's an alternative to choose your excel file. A window will pop up so that you can choose your file. — Paulo MiraMor, Jul 02 '15 at 12:39
I try the exactly way that you told me. The windows appear, but again i have the same error. Error:std::bad_alloc. The size of the file is 80 mb. — ribery77, Jul 02 '15 at 12:42
So it could be a memory limitation on your platform. Have you tried with a small subset of this file? — Paulo MiraMor, Jul 02 '15 at 13:01
I try with smaller excel file around 80kb it works. May I have memory limitation. — ribery77, Jul 02 '15 at 13:11
So it's probably a memory limitation. You could try exporting your file to .csv and read it using `read.csv`. This will require less memory. — Paulo MiraMor, Jul 02 '15 at 13:17

score 4 · Answer 1 · answered Jul 02 '15 at 12:26

First load the package:

library(readxl)

According to the package development page, you just have to specify the file name as a string, for example:

read_excel("my-old-spreadsheet.xls")
read_excel("my-new-spreadsheet.xlsx")

You can also specify the name or number of sheet:

read_excel("my-spreadsheet.xls", sheet = "data")
read_excel("my-spreadsheet.xls", sheet = 2)

Make sure you are in the correct directory using getwd(). If not, change it using setwd()

score 2 · Answer 2 · answered Mar 11 '16 at 20:26

2

As noted in the comments, that error message is caused by an issue with the file size. See the package issue at: https://github.com/hadley/readxl/issues/150.

answered Mar 11 '16 at 20:26

Sam Firke

21,571
9
87
105

I am getting the same error. read_excel worked for me but suddenly it has stopped working. – Polar Bear Aug 23 '16 at 08:31

score 1 · Answer 3 · answered Jul 24 '18 at 16:47

1

Just encountered this problem, when re-reading a file I read successfully!

As vladdsm points out on github, closing and re-opening Rstudio can solve the problem https://github.com/tidyverse/readxl/issues/150#issuecomment-236883769

answered Jul 24 '18 at 16:47

psychonomics

714
4
12
26

knb · Answer 4 · 2018-04-05T07:17:12.953

In my case I have opened the file in LibreOffice Calc (you could use Excel of course but I don't own that), removed the complex header rows containing merged cells and colored backgrounds etc, simplified it manually into a 1-row header, then saved the file. The new Excel file was much smaller, and Importing in with readxl::read_excel() was possible now.

Perhaps the software cleaned up the file internally in a different manner as well.

Of course this works only if importing into R is a one-off task, a single Excel file with a few sheets at most.

score 0 · Answer 5 · answered Nov 13 '19 at 18:38

0

I had the same issue while trying to read xlsx file into R environment. The file size is not large. The issue was resolved after i have reinstalled readxl package.

answered Nov 13 '19 at 18:38

coding_is_fun

117
1
14

score -1 · Answer 6 · answered Jun 30 '21 at 09:15

I guess its a problem with your memory. Maybe you had other programs open, so it was not enough memory to load and process the datafile in R. This might occur, if the datafile is large.

You can easily check that in you task manager while trying to read the xlsx file.

regards Basti

Error message using read_excel "Error: std::bad_alloc"

6 Answers6