如何将文件夹树中多个xlsx文件导入R？遇unzip错误求助

问题场景 #

路径C:/Users/XXX/Individual reports/下包含各州子文件夹，每个州文件夹内有2014-2023年的XLSX文件，文件名格式示例：(AK) FFY 2014 - Survey Results v1.xlsx。

尝试用以下代码批量读取文件：

library(tidyverse)
library(readxl)

file_path = ":/Users/XXX/Individual reports/"

df <- list.files(file_path,full.names = TRUE,pattern = ".xlsx",recursive = TRUE) %>%
  set_names(basename(.)) %>%
  map_df(.x = .,
         .f = read_xlsx,
         .id = "filename",
         sheet = "Table 5")

但触发错误：

> df <- list.files(file_path,full.names = TRUE,pattern = ".xlsx",recursive = TRUE) %>%
+   set_names(basename(.)) %>%
+   map_df(.x = .,
+          .f = read_xlsx,
+          .id = "filename",
+          sheet = 5)
Error in `map()`:
ℹ In index: 1.
ℹ With name: (AK) FFY 2014 - Synar Survey Results v1.xlsx.
Caused by error in `utils::unzip()`:
! zip file 'C:\Users\XXX\Individual reports\AK\(AK) FFY 2014 - Survey Results v1.xlsx' cannot be opened
Run `rlang::last_trace()` to see where the error occurred.

单独读取文件时也出现相同错误：

> df1 = read_xlsx(path = "C:/Users/XXX/Individual reports/AK/(AK) FFY 2014 - Survey Results v1.xlsx",
+                  sheet = 5)
Error in utils::unzip(zip_path, list = TRUE) : 
  zip file 'C:\Users\XXX\Individual reports\AK\(AK) FFY 2014 - Survey Results v1.xlsx' cannot be opened

错误原因 #

XLSX本质是ZIP压缩格式，readxl依赖utils::unzip解析，报错并非因为文件夹/文件被手动压缩，而是以下任一原因：

• 文件本身损坏（比如下载/保存时出错）
• 文件名包含特殊字符（如括号()）导致解析异常
• 文件被Excel或其他程序锁定
• readxl版本与文件格式不兼容

解决方案 #

1. 先排查单个文件的可用性 #

• 关闭所有打开目标XLSX文件的Excel窗口
• 手动打开文件，确认能正常加载，无损坏提示
• 尝试重命名文件，去掉括号等特殊字符（比如改为AK_FFY2014_Survey_Results_v1.xlsx），再用read_xlsx测试读取

2. 修正批量读取代码（带错误处理） #

如果单个文件测试正常，用以下代码批量读取，同时跳过无法读取的文件，并提取文件名、州信息：

library(tidyverse)
library(readxl)

file_path <- "C:/Users/XXX/Individual reports/"

# 获取所有XLSX文件的完整路径（正则匹配.xlsx需加转义符\\）
all_files <- list.files(file_path, full.names = TRUE, pattern = "\\.xlsx$", recursive = TRUE)

# 定义带错误捕获的读取函数
read_safe_xlsx <- function(file) {
  tryCatch(
    read_xlsx(file, sheet = "Table 5"),
    error = function(e) {
      message(paste("跳过无法读取的文件：", file))
      return(NULL)
    }
  )
}

# 批量读取并合并数据，同时添加文件元信息
df <- map_df(all_files, ~{
  # 从文件名提取州代码（比如从(AK)中提取AK）
  state_code <- str_extract(basename(.x), "\\(([A-Z]{2})\\)") %>% str_remove_all("\\(|\\)")
  # 读取文件并绑定元信息
  read_safe_xlsx(.x) %>%
    bind_cols(
      filename = basename(.x),
      state = state_code
    )
})

3. 备选方案：改用openxlsx包 #

如果readxl仍有兼容性问题，换用openxlsx包，它对特殊字符和部分损坏文件的兼容性更好：

library(tidyverse)
library(openxlsx)

file_path <- "C:/Users/XXX/Individual reports/"
all_files <- list.files(file_path, full.names = TRUE, pattern = "\\.xlsx$", recursive = TRUE)

read_safe_openxlsx <- function(file) {
  tryCatch(
    read.xlsx(file, sheet = 5),
    error = function(e) {
      message(paste("跳过文件：", file))
      return(NULL)
    }
  )
}

df <- map_df(all_files, ~{
  state_code <- str_extract(basename(.x), "\\(([A-Z]{2})\\)") %>% str_remove_all("\\(|\\)")
  read_safe_openxlsx(.x) %>%
    as_tibble() %>%  # 转换为tidyverse风格的数据框
    bind_cols(
      filename = basename(.x),
      state = state_code
    )
})

内容的提问来源于stack exchange，提问作者abrar