将一个大的xlsx文件导入R？

Question

我想知道是否有办法从一个“大”的xlsx文件（约20MB）中导入数据。我尝试使用xlsx和XLConnect库。不幸的是，两者都使用rJava，我总是得到相同的错误：

> library(XLConnect)
> wb <- loadWorkbook("MyBigFile.xlsx")
Error: OutOfMemoryError (Java): Java heap space

或者

> library(xlsx)
> mydata <- read.xlsx2(file="MyBigFile.xlsx")
Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
   java.lang.OutOfMemoryError: Java heap space

在加载rJava之前，我还尝试修改了java.parameters：

> options( java.parameters = "-Xmx2500m")
> library(xlsx) # load rJava
> mydata <- read.xlsx2(file="MyBigFile.xlsx")
Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
   java.lang.OutOfMemoryError: Java heap space

或者在加载rJava之后（我认为这有点愚蠢）：

> library(xlsx) # load rJava
> options( java.parameters = "-Xmx2500m")
> mydata <- read.xlsx2(file="MyBigFile.xlsx")
Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
   java.lang.OutOfMemoryError: Java heap space

但是什么都没有起作用。有人有想法吗？

- user2722443

---

您是否考虑将数据保存为更通用的格式，例如CSV？ - flodel

gdata 是另一个选项。我认为它不是基于 Java 的，但我可能弄错了。 - Ricardo Saporta

为什么这个文件这么大？有很多行（你需要全部吗？），有很多列（你需要全部吗？），有很多单独的工作表（你需要全部吗？），一个高分辨率的嵌入式图像（你不需要它...）？对于电子表格和其他二进制文件，以字节为单位的文件大小通常不是衡量其中数据大小的有用指标。 - Spacedman

"gdata" 的工作速度非常缓慢，每个表格大约需要 7 分钟才能完成，但它确实可以正常工作。 - user2722443

我一直在处理导入同事一个庞大的、公式密集的 Excel 文件（150 MB），而 gdata 是唯一能够完成这项任务的 Excel 包。就像这里所说，基于 Java 的包会用尽内存；openxlsx 则会崩溃。每个表格需要花费 30 分钟，但 gdata 完成了任务。 - Matt Parker

Answer 1

当有人给我发送（又一个）Excel文件进行分析时，我偶然发现了这个问题。虽然这个文件并不是很大，但出现了类似的错误：

java.lang.OutOfMemoryError: GC overhead limit exceeded

根据之前回答中@DirkEddelbuettel的评论，我安装了openxlsx包(http://cran.r-project.org/web/packages/openxlsx/)，然后运行了以下代码:

library("openxlsx")
mydf <- read.xlsx("BigExcelFile.xlsx", sheet = 1, startRow = 2, colNames = TRUE)

这正是我所寻找的。易于使用且非常快速。它成为了我的新挚友。感谢@DirkEddelbuettel的提示！

- orville jackson

---

我尝试了很多方法来读取一个大的 .xslx 文件，但似乎没有一种方法适用于我。当我使用 GitHub 上的 Schaun Wheeler 函数时出现了错误，而且我无法弄清楚如何在我的电脑上使用 gdata 中的 perl 命令。'openxlsx' 对我来说是个救星。感谢 @Dirk Eddelbuettel 和 Orville Jackson。 - nasia jaffri

你知道其他的解决方案吗？我找不到一种方法来使用openxlsx打开.xls文件。 - user124123

你可以尝试使用gdata包中的read.xls函数。我自己从未使用过，但值得一试。 - orville jackson

openxlsx是唯一可以处理我70Mo的Excel文件的库。但是我首先需要将文件从.xls转换为.xlsx格式。 - agenis

OpenXLSX的缺点是它无法识别日期。对我来说，从readxl包中使用read_excel似乎是正确的选择。 - peer

如果 openxlsx 也导致相同的错误。那么，如果在具有更改配置选项的数据湖上工作，则增加 RAM 大小。 - Abhishek

Answer 2

options(java.parameters = "-Xmx2048m")  ## memory set to 2 GB
library(XLConnect)

在加载任何Java组件之前，使用“选项”允许更多的内存。然后加载XLConnect库（它使用Java）。

就这样。使用readWorksheet等方法读取数据 ... 等等。

- viquanto

---

谢谢你的提示。需要注意的是，在使用R-Studio时，在执行require('rJava')之前，我必须先执行options(java.parameters = "-Xmx2048m")。不幸的是，我现在遇到了一个新的错误：“java.lang.OutOfMemoryError: GC overhead limit exceeded”，但我相信这是另一个问题。 - pbnelson

这对我有用，但我还必须确保我的R版本与Java版本匹配（例如，都是64位），并正确设置Java路径：options(java.parameters="-Xmx4g") # 增加java内存，Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jdk-11.0.2') # 适用于64位版本，library(rJava) # 检查它是否正常工作。 - Simon Woodward

Answer 3

我同意@orville jackson的回答，并且它确实帮助了我。

在@orville jackson提供的答案中，这里是如何使用openxlsx读写大文件的详细说明。

当数据量很小的时候，R有许多可以根据您的需求利用的包和函数。

write.xlsx、write.xlsx2、XLconnect也可以完成工作，但相比openxlsx有时会慢一些。

因此，如果你正在处理大型数据集并遇到java错误，我建议你看看"openxlsx"，它真的很棒，可以将时间缩短1/12。

我已经测试过所有方法，最后我对openxlsx的性能表现印象深刻。

以下是将多个数据集写入多个工作表的步骤。

install.packages("openxlsx")
library("openxlsx")

start.time <- Sys.time()

# Creating large data frame
x <- as.data.frame(matrix(1:4000000,200000,20))
y <- as.data.frame(matrix(1:4000000,200000,20))
z <- as.data.frame(matrix(1:4000000,200000,20))

# Creating a workbook
wb <- createWorkbook("Example.xlsx")
Sys.setenv("R_ZIPCMD" = "C:/Rtools/bin/zip.exe") ## path to zip.exe

Sys.setenv("R_ZIPCMD" = "C:/Rtools/bin/zip.exe")必须是静态的，因为它引用了来自Rtools的某个实用程序。

注意：如果您的系统中没有安装Rtools，请先安装它以获得良好的使用体验。以下是参考链接：（请选择适当的版本） https://cran.r-project.org/bin/windows/Rtools/

请根据以下链接中的选项进行检查（在安装过程中需要选择所有复选框）： https://cloud.githubusercontent.com/assets/7400673/12230758/99fb2202-b8a6-11e5-82e6-836159440831.png

# Adding a worksheets : parameters for addWorksheet are 1. Workbook Name 2. Sheet Name

addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")
addWorksheet(wb, "Sheet 3")

# Writing data in to respetive sheets: parameters for writeData are 1. Workbook Name 2. Sheet index/ sheet name 3. dataframe name

writeData(wb, 1, x)

# incase you would like to write sheet with filter available for ease of access you can pass the parameter withFilter = TRUE in writeData function.
writeData(wb, 2, x = y, withFilter = TRUE)

## Similarly writeDataTable is another way for representing your data with table formatting:

writeDataTable(wb, 3, z)

saveWorkbook(wb, file = "Example.xlsx", overwrite = TRUE)

end.time <- Sys.time()
time.taken <- end.time - start.time
time.taken

openxlsx软件包非常适合从/在Excel文件中读写大量数据，并且具有许多用于自定义格式的选项。

有趣的是，在这里我们不必担心Java堆内存。

- ayush varshney

---

已测试了read.xlsx2、XLConnect、readxl和openxlsx，其中openxlsx比其他工具快多次。 - Ali

Answer 4

我知道这个问题有点老，但现在有一个很好的解决方案。当你尝试使用GUI在Rstudio中导入Excel时，这是一个默认包，并且在我的情况下运行良好。

library(readxl)

data <- read_excel(filename)

- Doongsil

Answer 5

如Excel->R问题的规范化中提到的，最近出现了一种替代方法，来自readxl包，与例如openxlsx和xlsx相比，我发现它非常快速。

话虽如此，当电子表格大小超过一定限制时，您最好将其保存为.csv并使用fread。

- MichaelChirico

Answer 6

我在使用xlsx::read.xlsx和XLConnect::readWorksheetFromFile时也遇到了同样的错误。也许你可以使用RODBC::odbcDriverConnect和RODBC::sqlFetch，这两个函数使用Microsoft RODBC，效率更高。

- Jingnan Li

Answer 7

@flodel的建议是将文件转换为CSV格式，这似乎是最简单的方法。如果由于某种原因无法使用此方法，则可以分块读取文件：

 require(XLConnect)
 chnksz <- 2e3
 s <- <sheet>
 wb <- loadWorkbook(<file>, s)
 tot.rows <- getLastRow(wb)
 last.row =0
 for (i in seq(ceiling( tot.rows / chnksz) )) {
    next.batch <- readWorksheet(wb, s, startRow=last.row+i, endRow=last.row+chnksz+i)
    # optionally save next.batch to disk or 
    # assign it to a list. See which works for you. 
 } 

- Ricardo Saporta

---

不幸的是，loadWorkbook 命令会生成一个 "OutOfMemoryError" 错误。 基于同样的想法，我尝试了 mydata.chunk = read.xlsx2(file="MyBigFile.xlsx", sheetIndex=1, startRow=1, endRow=10)，但仍然出现相同的错误。 - user2722443

@user2722443，你是否正在保存已读取的部分，然后将其从内存中删除？另外，在每个循环中尝试运行gc()。这会减慢速度，但会清除一些内存。顺便问一下，你确定转换为CSV不是一个选择吗？ - Ricardo Saporta

实际上，mydata.chunk = read.xlsx2(file="MyBigFile.xlsx", sheetIndex=1, startRow=1, endRow=10) 会生成"OutOfMemoryError"。因此，我无法删除任何内容。关于CSV转换，这并非完全不可能，但它是一个外部操作（在加载到R之前）。 - user2722443

Answer 8

我在寻找答案时发现了这个帖子，问题与我的完全相同。我并没有试图在R中修改xlsx文件，而是使用Python将文件转换成.csv格式，然后使用标准扫描函数将文件导入到R中。

请查看：https://github.com/dilshod/xlsx2csv

- aaron

---

这就是在R的gdata包中已经有十年历史的内容（但幕后使用的是Perl）。 - Dirk Eddelbuettel

当我使用gdata解决问题时，速度非常慢。这个Python脚本可以快速转换大型xlsx文件。 - aaron

这个答案与另一个答案中提到的@flodel的建议有何不同？在我看来，RODBC比中间CSV格式有更少的优势。 - mlt

街角也有一个新人：openxlsx，它仅使用Rcpp和纯C++代码，声称速度非常快。不确定它有多精细。 - Dirk Eddelbuettel

为什么不直接在Excel中打开并导出为CSV呢？ - MattE