1

readxl_1.1.0

I'm trying to read the file from this link (US gov website)

https://www.cftc.gov/files/dea/history/dea_com_xls_2018.zip

When I unzip the xls file inside, and read with readxl::read_excel, it fails with the error message failed to open C:\path to file

I can open the file in excel, save it to csv and read it to R by fread, but there are a lot of those files, so that's tedious. By the way, some other xls files downloaded from the same webpage can be read by read_excel

jf328
  • 6,841
  • 10
  • 58
  • 82

1 Answers1

1

There's something odd about the xls file. I think it's because it contains some VBA code.

If you are happy to use XLConnect here is an alternative that reads the file.

library(XLConnect)
extdir = tempdir()
unzip("dea_com_xls_2018.zip", exdir = extdir)
file = list.files(extdir, pattern = 'xls', full.names = T)

wb = loadWorkbook(file)
ws = readWorksheet(wb, sheet = 1)

dim(ws)
#[1] 11131   126
Andrew Chisholm
  • 6,362
  • 2
  • 22
  • 41
  • Thanks. This works. Just to mention that answer from this post https://stackoverflow.com/q/7963393/1568919 also needs to be followed to increase JVM memory. – jf328 Nov 01 '18 at 11:14