XLConnect函数未将参数传递给system.file [英] XLConnect function is not passing argument to system.file

问题描述

我的XLConnect库有问题.我只看到它的system.file函数与

一起使用

file1 <- system.file(file, package ="XLConnect")

文件始终等于字符串的地方.但是我有一个字符串列表，其中包含每个文件的文件名.我在循环遍历列表的for循环中拥有此功能.无论出于何种原因，即使文件对象具有等效的"soandso.xlsx"，file1也为空.

所以我的问题是我需要file作为字符串吗?

编辑#1: 因此，我从使用给我一个单独的错误的XLConnect库切换到xlsx库，至少现在，尽管有不同的错误，我仍然可以运行代码.

我认为我正在取得进步，因为我的代码未能成功通过第一个循环(因为k增加到2)，并且我的数据集变量中填充了一些内容(我在其中存储了"read.xlsx2"响应)./p>

我已附上我当前错误的屏幕截图:

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

编辑#2:

https ://www.dropbox.com/s/tgaup2ihnlq7br2/Q4%2C%202015%20-%20Halton%2C%20condo%20types.xlsx?dl = 0

我已经附上了我需要附加的2/60文件.我目的地的所有文件都是Excel文件，其宽度为10列，行长可变-由于新区域或只是区域被重命名.我收到的新错误是:

此外:警告消息: 在unzip(xlsxFile，exdir = xmlDir)中:从zip文件提取错误1

编辑#3:

source('〜/.active-rstudio-document'，echo = TRUE)

> library("openxlsx")

> file_list = list.files(pattern = "xlsx$")

> dataset = data.frame()

> for (file in file_list){
+ 
+   print(file)
+   temp = read.xlsx(file,sheet = 1, colNames = F)
+   temp$quarter = substr(file,1,2)
+   temp$year = s .... [TRUNCATED] 
[1] "~$Q1, 2011 - Halton, all home types.xlsx"
Error in file(con, "r") : invalid 'description' argument
In addition: Warning message:
In unzip(xlsxFile, exdir = xmlDir) : error 1 in extracting from zip file

对不起，但是我仍然遇到问题，但是我知道问题出在哪里.由于某种原因，正在打印的文件前面带有〜$"，当我仅输入文件名"Q1，2011-Halton，all home types.xlsx"作为文件时，它可以工作，并且我得到的数据是输入到数据框.现在出现的问题我显然无法大量导入60个不同的Excel名称...

解决方案

尝试使用openxlsx库和以下代码:

library("openxlsx")

file_list = list.files(pattern = "xlsx$")

dataset = data.frame()

for (file in file_list){
    print(file)
    temp = read.xlsx(file,sheet = 1, colNames = F)
    temp$quarter = substr(file,1,2)
    temp$year = substr(file,4,8)
    dataset = rbind(dataset,temp)
}

修改1: 添加了一个print(file)来检测哪个xlsx文件存在解压缩问题.

修改2: 通过从文件名读取添加的季度和年份信息.仅当所有文件名都遵循相同的模式时，此方法才有效.

I have an issue with XLConnect library. Its system.file function, I've only seen be used with

file1 <- system.file(file, package ="XLConnect")

Where file is always equal to a string. However I have a list of strings with the file name of each file in it. I have this inside of a for loop that iterates through the list. For whatever reason, file1 is empty even though the file object has the equivalent "soandso.xlsx".

So my question is do I need file to be a string?

Edit #1: So I switched from using the XLConnect library which was giving me a separate error to xlsx library and at least now, I can get the code to run, albeit with different errors.

I think I am making progress though because I had the code unsuccessfully run past the first loop (since k incremented to 2) and I had something populate my dataset variable (where I store the 'read.xlsx2' reponse).

I've attached screenshot of my current error:

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

Edit # 2:

https://www.dropbox.com/s/s9413oee146v497/Q1%2C%202011%20-%20Halton%2C%20all%20home%20types.xlsx?dl=0 https://www.dropbox.com/s/tgaup2ihnlq7br2/Q4%2C%202015%20-%20Halton%2C%20condo%20types.xlsx?dl=0

I've included 2/60 file I need to attach. All files at my destination are Excel files, which are 10 columns wide and variable row length - due to new regions or just regions just being renamed. The new error I'm getting is :

In addition: Warning message: In unzip(xlsxFile, exdir = xmlDir) : error 1 in extracting from zip file

Edit#3:

source('~/.active-rstudio-document', echo=TRUE)

> library("openxlsx")

> file_list = list.files(pattern = "xlsx$")

> dataset = data.frame()

> for (file in file_list){
+ 
+   print(file)
+   temp = read.xlsx(file,sheet = 1, colNames = F)
+   temp$quarter = substr(file,1,2)
+   temp$year = s .... [TRUNCATED] 
[1] "~$Q1, 2011 - Halton, all home types.xlsx"
Error in file(con, "r") : invalid 'description' argument
In addition: Warning message:
In unzip(xlsxFile, exdir = xmlDir) : error 1 in extracting from zip file

Sorry but I'm still having issues, however I know where the problem is. For some reason what's being printed as file is coming with "~$" before it, when I input just the file name 'Q1, 2011 - Halton, all home types.xlsx' as the file, it works and I get data to be inputted into dataframe. Now the problem with this I obviously cannot mass import 60 different Excel names...

解决方案

Try using openxlsx library and the following code:

library("openxlsx")

file_list = list.files(pattern = "xlsx$")

dataset = data.frame()

for (file in file_list){
    print(file)
    temp = read.xlsx(file,sheet = 1, colNames = F)
    temp$quarter = substr(file,1,2)
    temp$year = substr(file,4,8)
    dataset = rbind(dataset,temp)
}

Edit 1: Added a print(file) to detect which xlsx file is having unzip problems.

Edit 2: Added quarter and year information by reading them from the filename. This only works if all filenames follow the same pattern.

这篇关于XLConnect函数未将参数传递给system.file的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！