40

I am using R with readxl package. I try to import an Excel file with the following command:

library(readxl)
city_codes <- read_excel("./data/file.xlsx", sheet = "city_codes")

It says it is a zip file and cannot be opened:

Error in sheets_fun(path) : 
Evaluation error: zip file './data/file.xlsx' cannot be opened.

Any ideas?

NelsonGon
  • 13,015
  • 7
  • 27
  • 57
canpoint
  • 817
  • 2
  • 9
  • 19

17 Answers17

35

I had this error, but for me, it was just that I had the sheet open in Excel while trying to read it into R. I guess the package wrongly perceives it as a zip file when it's trying to read it while Excel has partial ownership of it (and this blocks the read).

DHW
  • 1,157
  • 1
  • 9
  • 24
17

The error message is readxl's funny way of saying "file not found". That exact line of code gives me the exact same error, and the file doesn't even exist for me.

Note: I'm on version 1.0.0 of readxl

lebelinoz
  • 4,890
  • 10
  • 33
  • 56
  • exactly a path probem. When I moved the file under D:/ then worked. – canpoint Jun 18 '17 at 22:49
  • 18
    This message will also appear in you have the file opened in Excel. – vmb100 Feb 19 '18 at 14:08
  • 2
    Checking the full file path did not resolve this issue - I was reading multiple files from the same directory and only some would get that error. `file.exists()` was true for all the files, even the ones throwing the error message. Updating to version 1.1.0 resolved the error for me. That also resolved the error even if the XLSX file was still open as vmb100 had previously commented. – sbha Jun 01 '18 at 12:32
  • In version 1.1.0, when the file is not found, the error message is "Error: `path` does not exist: ‘some/path/name.xlsx’" and the OP error happens when the file is open in Excel – Melkor.cz Mar 14 '19 at 08:45
  • 3
    This message will also appear if the Excel file is encrypted and requires a password to open. See a discussion of the [issue here](https://github.com/tidyverse/readxl/issues/84). – Stefan Avey Aug 04 '20 at 18:49
  • 1
    I had the same error message. It was fixed when I deleted the non-English special character in the file name. – NCC1701 Jan 11 '21 at 09:18
  • My excel file was password protected. Removed using first step of this and it worked: https://www.passfab.com/excel/excel-password-remove.html – sbanders Jan 13 '21 at 03:14
  • The issue I found was that I run the code with one of the excel files opened as commented by @vmb100, After having closed it I run the code successfully. thanks! – Birasafab Mar 22 '22 at 12:09
6

For me the "Evaluation error: zip file" error message also appeared when the excel spreadsheet was still opened in MSExcel.

Jonas Engelhardt
  • 61
  • 1
  • 1
3

You can specify a path to a file, only if it is nested on the working directory. For example: If your working directory is MyWD and there is a folder in it, named MyData and another folder within MyData named MyNestedData, and finally myExcelFile.xlsx

read_excel("MyData/MyNestedData/myExcelFile.xlsx",sheet = "Sheet2") #will work
read_excel("MyWD/MyData/MyNestedData/myExcelFile.xlsx",sheet = "Sheet2") #will not work
Julio Alejandro Di Rienzo
  • 31
  • 1
3

If your excel worksheet is password protected, read_excel won't be able to access it and will give you this error. If it needs protection, I would suggest p/w protecting the folder it's in, and then unprotecting the worksheet.

Rich Rees
  • 41
  • 4
2

You may also get this error if you are using the wrong read function.

For example, read_xlsx("file.xls" ...) will throw the error.

ricardo
  • 8,195
  • 7
  • 47
  • 69
1

This can happen if you forget to do the following before you use the read_excel function

setwd("C:\\map\\map_in_map\\map_in_map_in_map_where_the_file_is")
Stef Vandeweyer
  • 11
  • 3
1

Try to change file restrictions if you are working on company's computer. This worked for me to solve this issue.

Aivaras Slivikas
  • 11
  • 2
0

You may try to specify the full path instead of ./path/to/file

WANNISA RITMAHAN
  • 381
  • 3
  • 4
0

Try to make a new file. click "save as" and make a new name. Run again your code. it worked well to me :)

jun ki
  • 1
0

Although the solution was already given, but for the sake of documentation I will leave my situation here:

I ran into the same problem and couldn't figure out why. It seems that my excel file had closed with an error or something similar. I had to open the excel file, and save it again. When I ran R, everything worked smoothly.

Mig
  • 139
  • 2
  • 10
0

The issue was that there was hidden files inside my working directory. I copied them to outside and exclude all directories. Now it works when I run the code.

Bruno Gomes
  • 91
  • 8
0

In my case the xlsx was created using Excel 2007, which caused this issue. Xlsx files from newer Excel versions work.

Ljupcho Naumov
  • 196
  • 2
  • 12
0

In my case the file was not visible in the directory although I had deleted it. I had to search for the file using a software called "Everyting" and I deleted it. After that it worked.

Baptista Zunguze
  • 111
  • 1
  • 5
0

For me, I was downloading a file from Google Drive into a tempfile with extension .xlsx... Realised the file in Google Drive was .xls so I changed it to that and it worked. Kinda rare situation though.

PulpDood
  • 411
  • 5
  • 9
0

I got the same error message. In my case the line that worked properly specified the sheet by a string (copied from excel). Suddenly, it did not work anymore (with the mentioned error message). I tried several things explained here. Ended up specifying the sheet by its number in the excel file - worked!

read_xlsx("table.xlsx", sheet="name_of_sheet") # suddently corrupt
read_xlsx("table.xlsx", sheet=4) # works

Why? No update of the package or something. I dislike to have to use a less specific line.

UPDATE: Restarting the R session made the initial line work again (although, I HAD checked the wd before...)

Clem Snide
  • 483
  • 6
  • 13
  • I do not see how this answers the question at the top of this page, but it should. Please [edit] according to [answer] or delete the answer. Otherwise it risks being flagged as "not an answer" and being deleted. – Yunnosch Sep 26 '22 at 13:49
0

As described in other answers, the error occurs for .xlsx files opened in Excel and synced with OneDrive/Sharpoint/Teams. The easy solution to read in the file is to close Excel and re-run readxl::read_excel() again.

However, sometimes it would be more convenient to keep it open and still be able to read it into R. This StackOverflow answer suggests to make a temporary copy. However, it uses file.copy() command which (at least for me) also does not work for open excel files.

The only solution which worked for me (on Windows) was to create a temporary copy of the file using the PowerShell command Copy-Item. See the function below:

read_excel_tmp <- function(path, sheet = NULL, range = NULL, col_names = TRUE,
                           col_types = NULL, na = "", trim_ws = TRUE,
                           skip = 0, n_max = Inf, guess_max = min(1000, n_max),
                           progress = readxl::readxl_progress(),
                           .name_repair = "unique"){
  destfile <- tempfile(fileext = ".xlsx")
  mycmd <- paste0("powershell -command \"Copy-Item '", gsub("/", "\\\\", path),
                  "' -Destination '", destfile, "'\"")
  error_code <- system(mycmd)
  if(error_code != 0) {stop("Powershell's `Copy-Item` was not able to copy-paste the file")}
  readxl::read_excel(path = destfile, sheet = sheet, range = range,
                     col_names = col_names, col_types = col_types, na = na,
                     trim_ws = trim_ws, skip = skip, n_max = n_max,
                     guess_max = guess_max, progress = progress,
                     .name_repair = .name_repair)
}

After you defined the function, the command below should also work for excel files which are currently opened

city_codes <- read_excel_tmp("./data/file.xlsx", sheet = "city_codes")

(Note: only works on Windows)

retodomax
  • 434
  • 3
  • 14