blocks|key|10366|text|@flodel关于转换为CSV的建议似乎是最直接的。如果出于某种原因，这不是一个选项，您可以分块读入文件：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|10367|+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%2Bi,+endRow=last.row%2Bchnksz%2Bi)
++++#+optionally+save+next.batch+to+disk+or+
++++#+assign+it+to+a+list.+See+which+works+for+you.+
+}+|code-block|syntax|javascript|10368|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

@flodel's suggestion of converting to CSV seems the most straightforward. If for whatever reason, that's not an option, you can read in the file in chunks: 

<pre><code> require(XLConnect)
 chnksz &lt;- 2e3
 s &lt;- &lt;sheet&gt;
 wb &lt;- loadWorkbook(&lt;file&gt;, s)
 tot.rows &lt;- getLastRow(wb)
 last.row =0
 for (i in seq(ceiling( tot.rows / chnksz) )) {
 next.batch &lt;- 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. 
 } 
</code></pre>

blocks|key|226980|text|我发现这个帖子在寻找完全相同的问题的答案。我没有尝试从R中破解xlsx文件，而是使用python将文件转换为.csv，然后使用标准扫描函数将文件导入R。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|226981|查看：https://github.com/dilshod/xlsx2csv|offset|length|226982|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/dilshod/xlsx2csv^0|0|3|Z|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

I found this thread looking for an answer to the exact same question. Rather than try to hack an xlsx file from within R what ended up working for me was to convert the file to .csv using python and then import the file into R using a standard scanning function. 

Check out: <a href="https://github.com/dilshod/xlsx2csv" rel="nofollow">https://github.com/dilshod/xlsx2csv</a>

blocks|key|10425|text|当有人给我发送(又一个)+Excel文件进行分析时，我偶然发现了这个问题。这个错误甚至没有那么大，但无论出于什么原因，我都遇到了类似的错误：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|10426|java.lang.OutOfMemoryError:+GC+overhead+limit+exceeded|code-block|syntax|javascript|10427|根据@DirkEddelbuettel在之前的回答中的评论，我安装了openxlsx包(http://cran.r-project.org/web/packages/openxlsx/)。然后运行：|offset|length|10428|library("openxlsx")
mydf+<-+read.xlsx("BigExcelFile.xlsx",+sheet+=+1,+startRow+=+2,+colNames+=+TRUE)|10429|这正是我要找的。简单易用，速度极快。这是我的新朋友。感谢你的提示@DirkEddelbuettel！|10430|顺便说一句，我不想从Dirk+E那里窃取这个答案，所以如果他发布了一个答案，请接受它，而不是我的！|10431|entityMap|0|LINK|mutability|MUTABLE|url|http://cran.r-project.org/web/packages/openxlsx/^0|0|0|18|1C|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@$I|11|J|12|1|13]]|A|$]]|$1|K|3|L|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

I stumbled on this question when someone sent me (yet another) Excel file to analyze. This one isn't even that big but for whatever reason I was running into a similar error:
<pre><code>java.lang.OutOfMemoryError: GC overhead limit exceeded
</code></pre>
Based on comment by @DirkEddelbuettel in a previous answer I installed the openxlsx package (<a href="http://cran.r-project.org/web/packages/openxlsx/" rel="noreferrer">http://cran.r-project.org/web/packages/openxlsx/</a>). and then ran:
<pre><code>library(&quot;openxlsx&quot;)
mydf &lt;- read.xlsx(&quot;BigExcelFile.xlsx&quot;, sheet = 1, startRow = 2, colNames = TRUE)
</code></pre>
It was just what I was looking for. Easy to use and wicked fast. It's my new BFF. Thanks for the tip @DirkEddelbuettel!
BTW, I don't want to poach this answer from Dirk E, so if he posts an answer, please accept it rather than mine!

blocks|key|227020|text|我在xlsx::read.xlsx和XLConnect::readWorksheetFromFile中也遇到了同样的错误。也许您可以使用RODBC::odbcDriverConnect和RODBC::sqlFetch，后者使用Microsoft+RODBC，效率更高。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|227021|entityMap^0|2|F|I|W|1W|O|2L|F|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|G|$]]

I also had the same error in both <code>xlsx::read.xlsx</code> and <code>XLConnect::readWorksheetFromFile</code>. Maybe you can use <code>RODBC::odbcDriverConnect</code> and <code>RODBC::sqlFetch</code>, which uses Microsoft RODBC, which is much more efficient.

blocks|key|227058|text|正如在the+canonical+Excel->R+question中提到的，最近出现的一种替代方案来自readxl包，与openxlsx和xlsx相比，I've+found包相当快。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|227059|也就是说，电子表格的大小是有一定限制的，超过这个限制，您最好将其保存为.csv并使用fread。|227060|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/6099243/read-an-excel-file-directly-from-a-r-script|1|https://stackoverflow.com/a/31734198/3576984^0|1G|6|1P|8|1Y|4|3|V|0|25|A|1|0|Z|4|16|5|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@$9|Y|A|Z|1|10]|$9|11|A|12|1|13]]|E|$]]|$1|F|3|G|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]|P|$5|K|L|M|E|$N|Q]]]]

As mentioned in <a href="https://stackoverflow.com/questions/6099243/read-an-excel-file-directly-from-a-r-script">the canonical Excel->R question</a>, a recent alternative which has emerged comes from the <code>readxl</code> package, which <a href="https://stackoverflow.com/a/31734198/3576984">I've found</a> to be quite fast, compared with, e.g. <code>openxlsx</code> and <code>xlsx</code>.

That said, there's a definite limit of spreadsheet size past which you're probably better off just saving the thing as a <code>.csv</code> and using <code>fread</code>.

blocks|key|10522|text|options(java.parameters+=+"-Xmx2048m")++##+memory+set+to+2+GB
library(XLConnect)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|10523|在加载任何java组件之前，使用“选项”允许更多的内存。然后加载XLConnect库(它使用java)。|unstyled|10524|就这样。开始使用readWorksheet读取数据...诸若此类。:)|10525|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>options(java.parameters = "-Xmx2048m") ## memory set to 2 GB
library(XLConnect)
</code></pre>

allow for more memory using "options" before any java component is loaded. Then load XLConnect library (it uses java).

That's it. Start reading in data with readWorksheet .... and so on.
:)

blocks|key|10579|text|我知道这个问题有点老了，但现在已经有了一个很好的解决方案。当你试图用GUI在Rstudio中导入excel时，这是一个默认的包，它在我的情况下工作得很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|10580|library(readxl)

data+<-+read_excel(filename)|code-block|syntax|javascript|10581|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I know this question is a bit old, but There is a good solution for this nowadays. This is a default package when you try to import excel in Rstudio with GUI and It works well in my situation.

<pre><code>library(readxl)

data &lt;- read_excel(filename)
</code></pre>

I'm wondering if anyone knows of a way to import data from a "big" xlsx file (~20Mb). I tried to use xlsx and XLConnect libraries. Unfortunately, both use rJava and I always obtain the same error:

<pre><code>&gt; library(XLConnect)
&gt; wb &lt;- loadWorkbook("MyBigFile.xlsx")
Error: OutOfMemoryError (Java): Java heap space
</code></pre>

or 

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

I also tried to modify the java.parameters before loading rJava:

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

or after loading rJava (this is a bit stupid, I think):

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

But nothing works. Does anyone have an idea?

Importing a big xlsx file into R?

我想知道是否有人知道从一个“大”的xlsx文件(~20Mb)导入数据的方法。我尝试使用xlsx和XLConnect库。不幸的是，两者都使用rJava，我总是得到相同的错误：> library(XLConnect)> wb <- loadWorkbook("MyBigFile.xlsx")Error: OutOfMemo...

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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