如何用R从指定网页自动下载CSV文件并转为DataFrame

在目标网页选择以下参数后点击下载按钮可得到对应CSV文件，现需用R实现自动获取并转为DataFrame：

• 类型：Futures
• 标的：CRA
• 起始日期：2023-03-16
• 结束日期：2023-03-16

之前的尝试方案（均未成功） #

尝试1：RSelenium模拟浏览器下载 #

# Load library ---------------------------------------------------------------
library("RSelenium")
library("tidyverse")

# The URL changes depending on what parameters are chosen
# So we don't have to use RSelenium to set parameters!
# It can be done with the URL

# Choose values------------------------------------------------------------

transaction_type <- "Futures"
symbol <- "CRA"
start_date <- "2023-03-16"
end_date <- "2023-03-16"


url <- paste0("https://www.m-x.ca/en/trading/data/historical?symbol=",
              symbol,
              "&from=",
              start_date,
              "&to=",
              end_date)


# I think this bit just specifies where on the page we start, so probably not
# needed:     #CRAH23-20230316


# start RSelenium ------------------------------------------------------------


# set up RSelenium ------------------------------------------------------
file_path <- getwd() |> str_replace_all("/", "\\\\\\\\")
fprof <- makeFirefoxProfile(list(browser.download.dir = file_path,
                                 browser.download.folderList = 2L,
                                 browser.download.manager.showWhenStarting = FALSE,
                                 browser.helperApps.neverAsk.openFile = "text/csv",
                                 browser.helperApps.neverAsk.saveToDisk = "text/csv")
)
remDr <- remoteDriver(extraCapabilities = fprof, port = 4445L)
remDr$open()

# Navigate to webpage -----------------------------------------------------
remDr$navigate(url)


# Click on the download button -----------------------------------------------------
remDr$findElements("id", "btnDnld")[[1]]$clickElement()

remDr$close()

尝试2：httr直接POST请求（下载到HTML而非CSV） #

library(httr)

POST("https://www.m-x.ca/en/trading/data/historical?symbol=CRA&from=2023-03-16&to=2023-03-16#CRAH23-20230316",
     body = list(`frmHisto[]` = "Futures", 
                 `symbol[]` = "CRA",
                 `from[]` = "2023-03-16",
                 `to[]` = "2023-03-16"), 
     encode = "form",
     write_disk("quote_CRA_20230316.csv")) -> res

可行实现方案 #

通过抓包分析，下载按钮实际调用的是网站专门的CSV导出接口，直接构造该接口的请求即可：

方案1：直接调用CSV导出接口 #

library(httr)
library(readr)

# 定义请求参数
request_params <- list(
  `frmHisto[]` = "Futures",
  `symbol[]` = "CRA",
  `from[]` = "2023-03-16",
  `to[]` = "2023-03-16"
)

# 发送POST请求到CSV导出接口
response <- POST(
  url = "https://www.m-x.ca/en/trading/data/historical_csv",
  body = request_params,
  encode = "form",
  user_agent("Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36")
)

# 检查请求是否成功
stop_for_status(response)

# 将响应内容直接转为DataFrame
target_df <- read_csv(content(response, "text"))

# 查看结果
head(target_df)

方案2：携带会话Cookie请求（应对网站会话验证） #

如果方案1因会话验证失败，可先请求页面获取Cookie再发送下载请求：

library(httr)
library(readr)

# 先请求页面获取会话Cookie
session <- GET(
  "https://www.m-x.ca/en/trading/data/historical",
  user_agent("Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36")
)

# 定义请求参数
request_params <- list(
  `frmHisto[]` = "Futures",
  `symbol[]` = "CRA",
  `from[]` = "2023-03-16",
  `to[]` = "2023-03-16"
)

# 携带Cookie发送下载请求
response <- POST(
  url = "https://www.m-x.ca/en/trading/data/historical_csv",
  body = request_params,
  encode = "form",
  user_agent("Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"),
  set_cookies(cookies(session))
)

stop_for_status(response)
target_df <- read_csv(content(response, "text"))
head(target_df)

内容的提问来源于stack exchange，提问作者ixodid