How to open a .xlsb file in R?

Question

I am trying to open a .xlsb file in R and keep getting similar errors.

Any recommendations for solving this problem without having to download data and save it in a different format?

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 books are not supported

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

Error messages: Error: XLSBUnsupportedException (Java): .xlsb Binary books are not supported

Note:

I can not install Perl libraries.

I am running 64bit R.

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

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

Answer 1

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) 

Answer 2

One way could be to use ODBC:

 require(RODBC) if (any(grepl("*.xlsb", odbcDataSources(), fixed = TRUE))) { download.file(url = "http://phx.corporate-ir.net/External.File?item=UGFyZW50SUQ9NTcwMjI1fENoaWxkSUQ9MjcxMjIxfFR5cGU9MQ==&t=1", destfile = file.path(tempdir(), "test.xlsb"), mode = "wb") conn <- odbcConnectExcel2007( file.path(tempdir(), "test.xlsb")) df <- sqlFetch(conn, sub("'(.*)\\$'", "\\1", sqlTables(conn)$TABLE_NAME)[4]) # read 4th sheet in the table name list head(df, 10) # F1 F2 F3 F4 F5 F6 # 1 <NA> <NA> <NA> <NA> <NA> NA # 2 <NA> <NA> <NA> <NA> <NA> NA # 3 <NA> <NA> <NA> <NA> <NA> NA # 4 <NA> <NA> <NA> <NA> <NA> NA # 5 Baker Hughes Gulf of Mexico Oil / Gas Split <NA> <NA> <NA> <NA> NA # 6 <NA> <NA> <NA> <NA> <NA> NA # 7 <NA> US Offshore Total\nGoM Gas\nGoM Oil \nGoM NA # 8 1/7/00 127 123 116 7 NA # 9 1/14/00 125 121 116 5 NA # 10 1/21/00 125 121 116 5 NA close(conn) } 

Answer 3

install.packages (RODBC) (RODBC) dataRead <- "Binary 2018-01-01.xlsb" ConnectionObj <-odbc (dataRead)

The above code does not work for me without the odbcConnectExcel2007 function in the "RODBC" package Error- {xml: code}

ConnectionObj <- odbcConnectExcel2007 (dataRead) Error in odbcConnectExcel2007 (dataRead): could not find the function "odbcConnectExcel2007" {code}

Answer 4

If you get the following error in R while trying to connect to .xlsb :

 [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 

then maybe you are missing the install AccessDatabaseEngine_X64.exe from Microsoft. I had this problem today and after installing this file I no longer have error messages.