How to open an .xlsb file in R?

r xlsb
user2946746 picture user2946746 · Feb 23, 2015 · Viewed 16.9k times · Source

I'm trying to open an .xlsb file in R and keep getting similar errors.

Any recommendations on how to solve this issue without having to download the data and save it in a different formate? 

read.xlsx(paste(OutputLocation,FileName, sep=""), sheetName = "Master Data")

Error messages: Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, : org.apache.poi.xssf.XLSBUnsupportedException: .XLSB Binary Workbooks are not supported

rigDataWB<-loadWorkbook(paste(OutputLocation,FileName, sep=""))

Error messages: Error: XLSBUnsupportedException (Java): .XLSB Binary Workbooks are not supported

Please note:

I cannot install Perl libraries.

I'm running 64bit R.

Reference: http://www.milanor.net/blog/?p=779

My data is from: http://phx.corporate-ir.net/phoenix.zhtml?c=79687&p=irol-reportsother

Answer

Anirban Pramanik picture Anirban Pramanik · Aug 9, 2016

Use the RODBC package: 

library(RODBC)
wb <- "D:\\Data\\Masked Data.xlsb" # Give the file name
con2 <- odbcConnectExcel2007(wb)
data <- sqlFetch(con2, "Sheet1$") # Provide name of sheet
nrow(data)

Related questions

How to read xlsb files?

I am trying to read a range into a data frame from a xlsb workbook. Can I do this with the xlsx package in R? If not, does anyone know of a way to do it?

Read More
Remove rows with all or some NAs (missing values) in data.frame

I'd like to remove the lines in this data frame that: a) contain NAs across all columns. Below is my example data frame. gene hsap mmul mmus rnor cfam 1 ENSG00000208234 0 NA NA NA NA 2 ENSG00000199674 0 2 2 2 2 3 ENSG00000221622 0 NA NA NA NA 4 …

Read More
Drop data frame columns by name

I have a number of columns that I would like to remove from a data frame. I know that we can delete them individually using something like: df$x <- NULL But I was hoping to do this with …

Read More