topic Re: Access EXCEL FILE using XLSX Engine in SAS Studio

Access EXCEL FILE using XLSX Engine

snsharma8930 — Wed, 12 Feb 2020 20:15:48 GMT

Hi Team,

I am trying to create library with XLSX engine & the library is creating successfully with XLSX Engine, however no XLSX file imported in the XLSX library. Please find below the code:

1) libname EXCELP XLSX '/home/snsharma89300/CertificationP/EXCELP.XLSX'; (underline is path of my sas studio library where i saved all xlsx files. )

below is log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

71 libname EXCELP XLSX '/home/snsharma89300/CertificationP/EXCELP.XLSX';

NOTE: Libref EXCELP was successfully assigned as follows:

Engine: XLSX

Physical Name: /home/snsharma89300/CertificationP/EXCELP.XLSX

72 run;

74 proc contents data= EXCELP._All_;

75 run;

WARNING: No matching members in directory.

Re: Access EXCEL FILE using XLSX Engine

PGStats — Wed, 12 Feb 2020 20:38:47 GMT

This occurs when the excel file doesn't exist. SAS assumes that you want to create the EXCELP.XLSX file.

Re: Access EXCEL FILE using XLSX Engine

snsharma8930 — Wed, 12 Feb 2020 20:56:24 GMT

below are the files which i already uploaded in the EXCELP folder.

EXCELP

file_example_XLSX_10 (1).xlsx

file_example_XLSX_10.xlsx

file_example_XLSX_100.xlsx

file_example_XLSX_50.xlsx

Re: Access EXCEL FILE using XLSX Engine

PGStats — Wed, 12 Feb 2020 21:13:14 GMT

You must refer to each file separately

'/home/snsharma89300/CertificationP/EXCELP/file_example_XLSX_10.xlsx'

Re: Access EXCEL FILE using XLSX Engine

snsharma8930 — Wed, 12 Feb 2020 21:16:28 GMT

Thanks for reply. If I refer the each file separately then file is successfully available in library, But my concern is why all files are not coming together.

Re: Access EXCEL FILE using XLSX Engine

Tom — Wed, 12 Feb 2020 21:51:02 GMT

@snsharma8930 wrote:

Thanks for reply. If I refer the each file separately then file is successfully available in library, But my concern is why all files are not coming together.

A libref in SAS points to a location with multiple individual datasets. An XLSX workbook consists of multiple individual worksheets. So when you make a libref using the XLSX engine you point it to one workbook. You can then use it to access all of the sheets in that one workbook. If you want to access all of the sheets in all of the workbooks then you will need to use multiple LIBNAME statements. Either with different libref names, or the same name over and over in a loop.

Re: Access EXCEL FILE using XLSX Engine

Quentin — Wed, 12 Feb 2020 22:15:05 GMT

Was going to add that you could use a concatenated library with the XLSX engine to point to multiple Excel files (assuming sheet names are unique).

But then I remembered that a couple years ago, I discovered it doesn't work.

https://communities.sas.com/t5/SAS-Programming/Does-XLSX-engine-support-concatenated-libraries/td-p/490037

I haven't tried it in more recent versions.

Re: Access EXCEL FILE using XLSX Engine

snsharma8930 — Wed, 12 Feb 2020 22:53:58 GMT

thanks for the response.