topic Importing issues - .Xlsb file(Excel Binary format) into SAS in SAS Programming

Importing issues - .Xlsb file(Excel Binary format) into SAS

jayakumarmm — Wed, 30 Nov 2016 17:00:35 GMT

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

Re: Importing issues - .Xlsb file(Excel Binary format) into SAS

rogerjdeangelis — Wed, 30 Nov 2016 17:48:50 GMT

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

Re: Importing issues - .Xlsb file(Excel Binary format) into SAS

rogerjdeangelis — Wed, 30 Nov 2016 17:50:59 GMT

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

Re: Importing issues - .Xlsb file(Excel Binary format) into SAS

rogerjdeangelis — Wed, 30 Nov 2016 17:54:45 GMT

Left out code and manual operation to create xlsb workbook

* create an xlsx dataset;
libname xel "d:/xls/class.xlsx";
data xel.classxlb;
set sashelp.class;
run;quit;
libname xel clear;

* open and save as xlsb;

Re: Importing issues - .Xlsb file(Excel Binary format) into SAS

jayakumarmm — Wed, 30 Nov 2016 18:09:28 GMT

Thanks a lot for your multiple solutions to address this issue. I am using SAS EG linux environment, please let me know whether these solutions are environment compatible i.e. Linux or Windows

Re: Importing issues - .Xlsb file(Excel Binary format) into SAS

Kurt_Bremser — Wed, 30 Nov 2016 18:19:50 GMT

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.

Re: Importing issues - .Xlsb file(Excel Binary format) into SAS

Vince_SAS — Thu, 01 Dec 2016 15:42:25 GMT

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