BookmarkSubscribeRSS Feed
jayakumarmm
Quartz | Level 8

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

 

6 REPLIES 6
rogerjdeangelis
Barite | Level 11
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
Quartz | Level 8
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
Barite | Level 11

 

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
Barite | Level 11
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
Super User

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.

Vince_SAS
Rhodochrosite | Level 12

 

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 10507 views
  • 0 likes
  • 4 in conversation