CODEWITHSUNDEEP
r
S3AN556
S3AN556

Reputation: 79

How to read a single file web page / web archive excel file into R

Is it possible to read this file into R without having to manually open the file myself and resave as an excel file. Below shows that this file is not actually an excel file.

https://www.sec.gov/Archives/edgar/data/320193/000119312511192493/Financial_Report.xls

url <- "https://www.sec.gov/Archives/edgar/data/320193/000119312511192493/Financial_Report.xls"
temp_xls <- tempfile(fileext = ".xls")
download.file(url, destfile = temp_xls, mode = "wb")

readxl::format_from_signature(temp_xls)
#> [1] NA

readr::read_lines(temp_xls, n_max = 5)
#> [1] "MIME-Version: 1.0"                                                                                                                                                                                                                                               
#> [2] "X-Document-Type: Workbook"                                                                                                                                                                                                                                       
#> [3] "Content-Type: multipart/related; boundary=\"----=_NextPart_e3144909_1d60_4840_908e_3419ae0a14d3\""                                                                                                                                                               
#> [4] ""                                                                                                                                                                                                                                                                
#> [5] "This document is a Single File Web Page, also known as a Web Archive file.  If you are seeing this message, your browser or editor doesn't support Web Archive files.  Please download a browser that supports Web Archive, such as Microsoft Internet Explorer."


unlink(temp_xls)

1) Is there any current solution to read this file into R

2) Is it possible to automate manually opening the file and saving it in a format that is readable in R

Upvotes: 1

Views: 961

Answers (1)

chinsoon12
chinsoon12

Reputation: 25225

A possible solution is to use httr:

library(httr)
library(XML)
library(magrittr)
h <- GET("https://www.sec.gov/Archives/edgar/data/320193/000119312511192493/Financial_Report.xls") %>% 
    content("text", encoding="UTF8") %>% 
    readHTMLTable()

edit: add a version that uses worksheets' names as names for the tables

library(httr)
library(XML)
library(xml2)
library(magrittr)
ctnt <- GET("https://www.sec.gov/Archives/edgar/data/320193/000119312511192493/Financial_Report.xls") %>% 
    content("text", encoding="UTF8") 
tbls <- readHTMLTable(ctnt)   
tbls <- tbls[names(tbls)!="NULL"]
names(tbls) <- read_html(gsub("<!--[if gte mso 9]>", "", ctnt, fixed=TRUE)) %>% 
    xml_find_all(".//name") %>% 
    xml_text()

Upvotes: 2

Related Questions