如何读取并合并Amazon S3中多份Excel文件指定工作表？

读取Amazon S3存储桶中数百份Excel工作簿里的特定工作表。

尝试方案 #

使用R语言的botor、openxlsx、tidyverse包编写代码，实现下载S3文件并提取第3个工作表第6行起的数据，再合并所有文件数据，代码如下：

library(botor)
library(openxlsx)
library(tidyverse)

# Function to download an Excel Workbook and extract the third sheet at row 6. 
read_simple <- function(FUN, s3_path, overwrite = TRUE) {
  tmp <- botor::s3_download_file(s3_path, tempfile(fileext = ".xlsx"), force = overwrite)
  FUN(tmp, startRow = 6, sheet = 3)
}

# Function to bind all files after some tidying
load_several_files <- function(template, list_of_files) {
  
  #create template file with all the correct column headings
  template_file <- read_simple(FUN = openxlsx::read.xlsx, s3_path = template)[0,] %>% 
    janitor::clean_names() 
  
  #take each file and then add the entries to the template - all the raw files have the same column headings
  for (each_file in list_of_files) {
    new_file <- read_simple(FUN = openxlsx::read.xlsx, s3_path = each_file) %>% 
      janitor::clean_names() 
    template_file <- template_file %>% bind_rows(new_file)
  }
  return(template_file)
}

#The following produces a list of the links to files in the bucket
list_files <- botor::s3_ls('s3://my_bucket/')
final_list <- list_files[2:nrow(list_files),3]
final_list

#I use the first file in the folder as the template and then try to add all the other files in the bucket. 
load_several_files("s3://my_bucket/file1.xlsx", final_list)

问题 #

执行上述代码后，无法得到包含所有数据的最终模板文件。

解决建议 #

1. 修正文件列表获取逻辑 #

原代码用索引提取路径容易因s3_ls返回结构变化出错，改为按列名提取并过滤Excel文件：

list_files <- botor::s3_ls('s3://my_bucket/')
# 按列名提取S3路径（s3_ls返回结果通常含`path`列）
final_list <- list_files$path
# 仅保留.xlsx后缀文件，排除文件夹或其他格式
final_list <- final_list[grepl("\\.xlsx$", final_list)]

2. 重构文件读取与合并逻辑 #

替换低效的for循环，用purrr::map_dfr批量读取并自动合并，同时添加错误捕获避免流程中断：

load_several_files <- function(list_of_files) {
  # 单个文件读取函数，含错误处理
  read_single_file <- function(s3_path) {
    tryCatch({
      tmp <- botor::s3_download_file(s3_path, tempfile(fileext = ".xlsx"), force = TRUE)
      openxlsx::read.xlsx(tmp, startRow = 6, sheet = 3) %>%
        janitor::clean_names()
    }, error = function(e) {
      message(paste("读取失败：", s3_path, " | 错误：", e$message))
      return(tibble()) # 返回空表，不影响整体合并
    })
  }
  
  # 批量读取并合并为单个数据框
  purrr::map_dfr(list_of_files, read_single_file)
}

3. 调用修正后的函数 #

final_data <- load_several_files(final_list)
# 查看合并结果
head(final_data)

额外排查点 #

• 确认所有目标Excel文件的第3个工作表存在，且第6行是数据表头
• 检查botor的S3权限配置，确保能访问存储桶内所有文件
• 用head(final_list)确认文件路径为s3://my_bucket/xxx.xlsx的正确格式

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