如何在R中打开.xlsb文件？

Question

我正在尝试在R中打开一个.xlsb文件，但一直收到类似的错误。

有没有建议可以解决这个问题而不必下载数据并将其保存为不同格式？

read.xlsx(paste(OutputLocation,FileName, sep=""), sheetName = "Master Data")

错误信息： 在 .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, 时出现错误： org.apache.poi.xssf.XLSBUnsupportedException: 不支持 .XLSB 二进制工作簿

rigDataWB<-loadWorkbook(paste(OutputLocation,FileName, sep=""))

错误信息： 错误：XLSBUnsupportedException（Java）：不支持.XLSB二进制工作簿

请注意：

我无法安装Perl库。

我正在运行64位R。

参考文献： http://www.milanor.net/blog/?p=779

我的数据来自： http://phx.corporate-ir.net/phoenix.zhtml?c=79687&p=irol-reportsother

- user2946746

Answer 1

使用 RODBC 包：

library(RODBC)
wb <- "D:\\Data\\Masked Data.xlsb" # Give the file name
con2 <- odbcConnectExcel2007(wb)
data <- sqlFetch(con2, "Sheet1$") # Provide name of sheet
nrow(data)

- Anirban Pramanik

Answer 2

一种方法是使用ODBC：

require(RODBC)
if (any(grepl("*.xlsb", odbcDataSources(), fixed = TRUE))) {
  download.file(url = "http://phx.corporate-ir.net/External.File?item=UGFyZW50SUQ9NTcwMjI1fENoaWxkSUQ9MjcxMjIxfFR5cGU9MQ==&t=1", 
                destfile = file.path(tempdir(), "test.xlsb"), 
                mode = "wb")
  conn <- odbcConnectExcel2007( file.path(tempdir(), "test.xlsb")) 
  df <- sqlFetch(conn, sub("'(.*)\\$'", "\\1", sqlTables(conn)$TABLE_NAME)[4]) # read 4th sheet in the table name list
  head(df, 10)
  #                                             F1          F2         F3       F4        F5 F6
  # 1                                         <NA>        <NA>       <NA>     <NA>      <NA> NA
  # 2                                         <NA>        <NA>       <NA>     <NA>      <NA> NA
  # 3                                         <NA>        <NA>       <NA>     <NA>      <NA> NA
  # 4                                         <NA>        <NA>       <NA>     <NA>      <NA> NA
  # 5  Baker Hughes Gulf of Mexico Oil / Gas Split        <NA>       <NA>     <NA>      <NA> NA
  # 6                                         <NA>        <NA>       <NA>     <NA>      <NA> NA
  # 7                                         <NA> US Offshore Total\nGoM Gas\nGoM Oil \nGoM NA
  # 8                                       1/7/00         127        123      116         7 NA
  # 9                                      1/14/00         125        121      116         5 NA
  # 10                                     1/21/00         125        121      116         5 NA
  close(conn) 
}

- lukeA

---

简单说明一下，如果你遇到错误，请更新你的Windows驱动程序在这里，这个解决方案有效。 - hubbs5

嗨。这个答案现在过时了吗？在RODBC中似乎没有odbcConnectExcel2007。或者这只适用于Windows？ - user2957945

我正在使用Windows系统，odbcConnectExcel2007确实存在。 - Fabian Pino

Answer 3

readxlsb包能够将Excel二进制(.xlsb)文件读入R中。以下是一些从该包vignettes中摘录的信息：

read_xlsb(path, sheet, range, col_names, col_types, na, trim_ws, skip, ...)

sheet:

要读取的表格名称或索引。第一个表格的索引为1。如果表格名嵌入在range参数中，或者如果range是命名范围，则忽略此参数。

range:

range可以指定为：

• 一个命名范围。 命名范围不区分大小写。
• 以Sheet!A1表示法表示
• 以Sheet!R1C1表示法表示
• 作为cellranger :: cell_limits对象

col_names

• TRUE：使用第一行作为列名。 空单元格导致列名形式为“column.i”
• FALSE：列名将为“column.i”
• 字符向量：包含列名称的向量。

col_types

可以从电子表格中推断出类型，也可以事先指定。当指定类型时，选项为

• "logical"（或"boolean"），"numeric"（或"double"），"integer"，"date"和"string"（或"character"）
• 使用"skip"（或"ignore"）跳过列

na

将解释为NA的字符字符串。 这不会影响列的暗示数据类型。

trim_ws

是否应从字符字符串中修剪前导和尾随空格？

skip

在读取数据之前要跳过的行数。

library(readxlsb)

res = read_xlsb(path = system.file("extdata", "TestBook.xlsb", package = "readxlsb"), 
                range = "PORTFOLIO", 
                debug = TRUE)

ls(res$env)
#> [1] "content"      "named_ranges" "sheets"       "stream"

res$env$named_ranges
#>             name                     range sheet_idx first_column first_row
#> 1   INFO_RELEASE          FirstSheet!$A$11         0            1        11
#> 2        OUTLOOK 'My SecondTab'!$A$1:$C$13         1            1         1
#> 3      PORTFOLIO      FirstSheet!$A$3:$C$9         0            1         3
#> 4 SAVED_DATETIME          FirstSheet!$C$13         0            3        13
#> 5          TITLE           FirstSheet!$A$1         0            1         1
#>   last_column last_row
#> 1           1       11
#> 2           3       13
#> 3           3        9
#> 4           3       13
#> 5           1        1

^{此内容由 reprex package（v0.3.0）于2020-07-07创建。}

- Tung

---

readxlsb 函数对于任何有意义大小的 XLSB 文件来说速度都非常缓慢，而且经常无法成功完成。虽然没有真正的替代品，但用户在编写依赖于 readxlsb 的代码时应该注意这一点。 - tchakravarty

这个包不起作用，会导致R Studio冻结。 - Adnan Hajizada

这是在Linux上对我有效的唯一答案。处理10万条记录非常缓慢，但最终成功了。 - user2957945

这个软件包的功能非常有限，例如我无法在读取时跳过NA或空单元格。 - Duffer

@Duffer：你可以尝试使用Python包（pyxlsb 或 pyxlsb2 通过 reticulate）来解决 readxlsb 包的一些限制。不过我还没有测试过它们。 - Tung

Answer 4

如果您在尝试连接 .xlsb 时，在 R 中遇到以下错误：

[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

那么，您可能忘记安装来自Microsoft的AccessDatabaseEngine_X64.exe。我今天遇到了这个问题，在安装了这个文件之后，我就不再收到错误消息了。

- braga461

Answer 5

尝试

xl.read.file(file_path, h=T, top.left.cell = "A1") from library(excel.link) 

- Sandeep

---

你能否添加一份关于你的代码如何工作的解释？ - Ruli