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

jayakumarmm · Posted 11-30-2016 11:54 AM

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

rogerjdeangelis · Posted 11-30-2016 12:48 PM

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

jayakumarmm · Posted 11-30-2016 01:09 PM

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

rogerjdeangelis · Posted 11-30-2016 12:50 PM

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

rogerjdeangelis · Posted 11-30-2016 12:54 PM

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;

Kurt_Bremser · Posted 11-30-2016 01:19 PM

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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Vince_SAS · Posted 12-01-2016 10:42 AM

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

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