读取Excel文件: How to find the start cell in messy spreadsheets?

Question

我正在尝试编写 R 代码来从一堆旧电子表格中读取数据。数据的确切位置因工作表而异:唯一不变的是第一列是日期，第二列的标题为“月度返回”。在此示例中，数据从单元格 B5 开始:

如何使用 R 自动在 Excel 单元格中搜索“每月报表”字符串？

目前，我能想到的最好的想法是从单元格 A1 开始上传 R 中的所有内容，并整理出结果(巨大)矩阵中的困惑情况。我希望有一个更优雅的解决方案

Answer 1

我还没有找到一种方法来优雅地做到这一点，但我非常熟悉这个问题(从 FactSet PA 报告 -> Excel -> R 获取数据，对吧？)。我知道不同的报告有不同的格式，这可能会很痛苦。

对于格式稍有不同的电子表格版本，我执行以下操作。它不是最优雅的(它需要两次读取文件)，但它可以工作。我喜欢阅读文件两次，以确保列的类型正确，并且标题正确。很容易搞乱列导入，因此我宁愿让我的代码读取文件两次，也不愿自己遍历并清理列，并且如果从正确的行开始，则 read_excel 默认值非常好。

此外，值得注意的是，截至今天(2017-04-20)，readxl had an update 。我安装了新版本，看看这是否会让这变得很容易，但我不相信情况是这样，尽管我可能是错的。

library(readxl)
library(stringr)
library(dplyr)

f_path <- file.path("whatever.xlsx")

if (!file.exists(f_path)) {
  f_path <- file.choose()
}

# I read this twice, temp_read to figure out where the data actually starts...

# Maybe you need something like this - 
#   excel_sheets <- readxl::excel_sheets(f_path)
#   desired_sheet <- which(stringr::str_detect(excel_sheets,"2 Factor Brinson Attribution"))
desired_sheet <- 1
temp_read <- readxl::read_excel(f_path,sheet = desired_sheet)

skip_rows <- NULL
col_skip <- 0
search_string <- "Monthly Returns"
max_cols_to_search <- 10
max_rows_to_search <- 10

# Note, for the - 0, you may need to add/subtract a row if you end up skipping too far later.
while (length(skip_rows) == 0) {
  col_skip <- col_skip + 1
  if (col_skip == max_cols_to_search) break
  skip_rows <- which(stringr::str_detect(temp_read[1:max_rows_to_search,col_skip][[1]],search_string)) - 0

}

# ... now we re-read from the known good starting point.
real_data <- readxl::read_excel(
  f_path,
  sheet = desired_sheet,
  skip = skip_rows
)

# You likely don't need this if you start at the right row
# But given that all weird spreadsheets are weird in their own way
# You may want to operate on the col_skip, maybe like so:
# real_data <- real_data %>%
#   select(-(1:col_skip))