XLConnect的无效“路径"参数 [英] Invalid 'path' argument with XLConnect

问题描述

我正在尝试并且无法在R版本3.1.2中完成以下过程:

I am trying and failing to get the following process to complete in R Version 3.1.2:

library(RCurl)
library(XLConnect)
yr <- substr(Sys.Date(), 1, 4)
mo <- as.character(as.numeric(substr(Sys.Date(), 6, 7)) - 1)
temp <- tempfile()
temp <- getForm("http://strikemap.clb.org.hk/strikes/api.v4/export",
  FromYear = "2011", FromMonth = "1", 
  ToYear = yr, ToMonth = mo,
  `_lang` = "en")
CLB <- readWorksheetFromFile(temp, sheet=1)
unlink(temp)

我已经能够手动导出请求的数据集，然后使用相同的readWorksheetFromFile语法从本地目录中将其读取到R中.我现在的目标是在R中做所有事情.对API的调用似乎可以正常工作(感谢先前的帮助)，但是当我尝试提取结果时，该过程在下一步失败.这是发生了什么:

I have been able manually to export the requested data set and then read it into R from a local directory using the same readWorksheetFromFile syntax. My goal now is to do the whole thing in R. The call to the API seems to work (thanks to some earlier help), but the process fails at the next step, when I try to ingest the results. Here's what happens:

> CLB <- readWorksheetFromFile(temp, sheet=1)
Error in path.expand(filename) : invalid 'path' argument

对我做错了什么或坏了有什么想法吗?

Any thoughts on what I'm doing wrong or what's broken?

推荐答案

原来，问题根本不在于XLConnect.根据Hadley的提示，在将查询结果读回R之前，我需要将查询结果保存到API到文件中，我已经设法(几乎)使用以下代码完成了该过程:

Turns out the problem didn't lie with XLConnect at all. Based on Hadley's tip that I needed to save the results of my query to the API to a file before reading them back into R, I have managed (almost) to complete the process using the following code:

library(httr)
library(readxl)
yr <- substr(Sys.Date(), 1, 4)
mo <- as.character(as.numeric(substr(Sys.Date(), 6, 7)) - 1)
baseURL <- paste0("http://strikemap.clb.org.hk/strikes/api.v4/export?FromYear=2011&FromMonth=1&ToYear=", yr, "&ToMonth=", mo, "&_lang=en")
queryList <- parse_url(baseURL)
clb <- GET(build_url(queryList), write_disk("clb.temp.xlsx", overwrite=TRUE))
CLB <- read_excel("clb.temp.xlsx")

创建的对象CLB包含一个小故障所需的数据:第一列中的日期未正确读取.如果我在Excel中打开"clb.temp.xlsx"，它们将按预期显示(例如，2015-06-30或6/30/2015，如果我单击该单元格).但是read_excel()会将其读取为数字，而这些数字并未以明显的方式跟踪这些日期(例如，对于2015-06-30，为42185).我尝试通过指定它们为read_excel调用中的日期来解决此问题，但这会产生一连串的警告，提示您期望日期但获得这些数字.

The object that creates, CLB, includes the desired data with one glitch: the dates in the first column are not being read properly. If I open "clb.temp.xlsx" in Excel, they show up as expected (e.g., 2015-06-30, or 6/30/2015 if I click on the cell). But read_excel() is reading them as numbers that don't track to those dates in an obvious way (e.g., 42185 for 2015-06-30). I tried fixing that by specifying that they were dates in the call to read_excel, but that produced a long string of warnings about expecting dates but getting those numbers.

如果在最后一步中使用readWorkSheetFromFile()而不是read_excel，则会发生以下情况:

If I use readWorkSheetFromFile() instead of read_excel at that last step, here's what happens:

> CLB <- readWorksheetFromFile("clb.temp.xlsx")
Error in (function (classes, fdef, mtable)  : unable to find an inherited method for function ‘readWorksheet’ for signature ‘"workbook", "missing"’

我将使用read_excel搜索该问题的解决方案，如果找不到，将创建一个新问题.

I will search for a solution to the problem using read_excel and will create a new question if I can't find one.

这篇关于XLConnect的无效“路径"参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！