How to download an .xlsx file in R and load the data into a dataframe?

r import-from-excel xlconnect
user2946746 picture user2946746 · Mar 4, 2015 · Viewed 7.9k times · Source

I'm trying to download an .xlsx file from the eia and getting the following error.

The error is: "Error: ZipException (Java): invalid entry size (expected 2385 but got 2390 bytes)"

I have tried the following code:

library(XLConnect)
tmp = tempfile(fileext = ".xlsx")
download.file(url = "http://www.eia.gov/petroleum/drilling/xls/dpr-data.xlsx", destfile = tmp)
readWorksheetFromFile(file = tmp, sheet = "Eagle Ford Region", header = FALSE, startRow = 9, endRow = 151)

I have tried the other recommendations at: Read Excel file into R with XLConnect package from URL

Answer

m0nhawk picture m0nhawk · Mar 4, 2015

You should use wb - binary mode while downloading the files, that are not plain text:

download.file(url = "http://www.eia.gov/petroleum/drilling/xls/dpr-data.xlsx", destfile = tmp, mode="wb")

This will solve the issue.

Related questions

Accessing Excel file from Sharepoint with R

am trying to write an R script that will access an Excel file that is stored on my company's Sharepoint page so that I can make a few calculations and plot the results. I've tried various ways to do this (…

Read More
sql command for reading a particular sheet, column

This is probably a very stupid question for SQL stalwarts, but I just want one SQL command. Details, I am using a data analysis tool called R, this tool uses ODBC to read data from XLS. I am now trying …

Read More
Loading data from Excel file into R or Octave

I have an Excel file with a large set of data. The built-in graphs available in Excel are not enough to analyze these data, so I am thinking about using some tool like octave or R. I was thinking about …

Read More