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
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)
}
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)
install.packages("RODBC")
library(RODBC)
dataRead <-"Binary 2018-01-01.xlsb"
ConnectionObj <-odbc (dataRead)
The above code is not working for me no function as odbcConnectExcel2007 in "RODBC" package
Error-
{xml:code}
ConnectionObj <- odbcConnectExcel2007(dataRead)
Error in odbcConnectExcel2007(dataRead) :
could not find function "odbcConnectExcel2007"
{code}