I am trying to import .xlsb excel files into sas using below code. This code works perfectly for xlsm & Xlsx format files but it is not working for .xlsb files.Please assist.
Code:
libname xlsFile1 XLSX "/path/file_name.xlsb";
options validvarname=v7;
options SYMBOLGEN MPRINT;
options errorabend;
PROC SQL;
create table work.raw_data as
(select * from xlsFile1.data);
run;
Error:
Could not find 'workbook' entry in xlsx file. rc=8014900E (-2146136050) Requested Input File Is Invalid
Here are three solutions
___ __ __ _
|_ _| \/ | |
| || |\/| | |
| || | | | |___
|___|_| |_|_____|
proc iml;
submit / R;
library(RODBC);
wb <- "d:/xls/class.xlsb";
con2 <- odbcConnectExcel2007(wb);
data <- sqlFetch(con2, "class");
data;
endsubmit;
call importdatasetfromr('data','data');
quit;
____
| _ \
| |_) |
| _ <
|_| \_\
%utl_submit_r64('
library(RODBC);
wb <- "d:/xls/class.xlsb";
con2 <- odbcConnectExcel2007(wb);
data <- sqlFetch(con2, "classxlb");
data;
');
> library(RODBC);wb <- "d:/xls/class.xlsb";
con2 <- odbcConnectExcel2007(wb);
data <- sqlFetch(con2, "classxlb");
data;
NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
3 Barbara F 13 65.3 98.0
4 Carol F 14 62.8 102.5
5 Henry M 14 63.5 102.5
6 James M 12 57.3 83.0
7 Jane F 12 59.8 84.5
8 Janet F 15 62.5 112.5
9 Jeffrey M 13 62.5 84.0
10 John M 12 59.0 99.5
11 Joyce F 11 51.3 50.5
12 Judy F 14 64.3 90.0
13 Louise F 12 56.3 77.0
14 Mary F 15 66.5 112.0
15 Philip M 16 72.0 150.0
16 Robert M 12 64.8 128.0
17 Ronald M 15 67.0 133.0
18 Thomas M 11 57.5 85.0
19 William M 15 66.5 112.0
>
__ ______ ____
\ \ / / _ \/ ___|
\ \ /\ / /| |_) \___ \
\ V V / | __/ ___) |
\_/\_/ |_| |____/
%utl_submit_wps64('
options set=R_HOME "C:/Program Files/R/R-3.2.4";
libname saswork "%sysfunc(pathname(work))";
proc r;
submit;
library(RODBC);
wb <- "d:/xls/class.xlsb";
con2 <- odbcConnectExcel2007(wb);
classxlb <- sqlFetch(con2, "classxlb");
classxlb;
endsubmit;
import r=classxlb data=saswork.classxlb;
run;quit;
');
proc print data=classxlb;
run;quit;
Up to 40 obs from classxlb total obs=19
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
3 Barbara F 13 65.3 98.0
4 Carol F 14 62.8 102.5
5 Henry M 14 63.5 102.5
6 James M 12 57.3 83.0
7 Jane F 12 59.8 84.5
8 Janet F 15 62.5 112.5
9 Jeffrey M 13 62.5 84.0
10 John M 12 59.0 99.5
11 Joyce F 11 51.3 50.5
12 Judy F 14 64.3 90.0
13 Louise F 12 56.3 77.0
14 Mary F 15 66.5 112.0
15 Philip M 16 72.0 150.0
16 Robert M 12 64.8 128.0
17 Ronald M 15 67.0 133.0
18 Thomas M 11 57.5 85.0
19 William M 15 66.5 112.0
Here are three solutions
___ __ __ _
|_ _| \/ | |
| || |\/| | |
| || | | | |___
|___|_| |_|_____|
proc iml;
submit / R;
library(RODBC);
wb <- "d:/xls/class.xlsb";
con2 <- odbcConnectExcel2007(wb);
data <- sqlFetch(con2, "class");
data;
endsubmit;
call importdatasetfromr('data','data');
quit;
____
| _ \
| |_) |
| _ <
|_| \_\
%utl_submit_r64('
library(RODBC);
wb <- "d:/xls/class.xlsb";
con2 <- odbcConnectExcel2007(wb);
data <- sqlFetch(con2, "classxlb");
data;
');
> library(RODBC);wb <- "d:/xls/class.xlsb";
con2 <- odbcConnectExcel2007(wb);
data <- sqlFetch(con2, "classxlb");
data;
NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
3 Barbara F 13 65.3 98.0
4 Carol F 14 62.8 102.5
5 Henry M 14 63.5 102.5
6 James M 12 57.3 83.0
7 Jane F 12 59.8 84.5
8 Janet F 15 62.5 112.5
9 Jeffrey M 13 62.5 84.0
10 John M 12 59.0 99.5
11 Joyce F 11 51.3 50.5
12 Judy F 14 64.3 90.0
13 Louise F 12 56.3 77.0
14 Mary F 15 66.5 112.0
15 Philip M 16 72.0 150.0
16 Robert M 12 64.8 128.0
17 Ronald M 15 67.0 133.0
18 Thomas M 11 57.5 85.0
19 William M 15 66.5 112.0
>
__ ______ ____
\ \ / / _ \/ ___|
\ \ /\ / /| |_) \___ \
\ V V / | __/ ___) |
\_/\_/ |_| |____/
%utl_submit_wps64('
options set=R_HOME "C:/Program Files/R/R-3.2.4";
libname saswork "%sysfunc(pathname(work))";
proc r;
submit;
library(RODBC);
wb <- "d:/xls/class.xlsb";
con2 <- odbcConnectExcel2007(wb);
classxlb <- sqlFetch(con2, "classxlb");
classxlb;
endsubmit;
import r=classxlb data=saswork.classxlb;
run;quit;
');
proc print data=classxlb;
run;quit;
Up to 40 obs from classxlb total obs=19
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
3 Barbara F 13 65.3 98.0
4 Carol F 14 62.8 102.5
5 Henry M 14 63.5 102.5
6 James M 12 57.3 83.0
7 Jane F 12 59.8 84.5
8 Janet F 15 62.5 112.5
9 Jeffrey M 13 62.5 84.0
10 John M 12 59.0 99.5
11 Joyce F 11 51.3 50.5
12 Judy F 14 64.3 90.0
13 Louise F 12 56.3 77.0
14 Mary F 15 66.5 112.0
15 Philip M 16 72.0 150.0
16 Robert M 12 64.8 128.0
17 Ronald M 15 67.0 133.0
18 Thomas M 11 57.5 85.0
19 William M 15 66.5 112.0
xlsb is a binary format that does not follow the "XML in ZIP" principle (which is openly documented) of xlsx and needs (just like the old formats) a MS module to open. This module is not available on UNIX.
Bottom line: while xlsx is just not suited as a data transfer format (see the gazillion of SAS community threads about this), xlsb is unusable.
To read and write native Excel files you must license and install the SAS/ACCESS Interface to PC Files:
http://support.sas.com/documentation/cdl/en/acpcref/69731/HTML/default/viewer.htm#titlepage.htm
I started a PC Files Server on a Windows machine, placed an XLSB file in C:\temp\ on that same Windows machine, and then successfully ran this code from SAS Enterprise Guide connected to a Linux server running SAS 9.4 M3:
proc import out=work.class
file='C:\temp\Class.xlsb'
replace
dbms=excelcs;
server='my.domain.com';
port=9621;
run; quit;
Vince DelGobbo
SAS R&D
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.