Access EXCEL FILE using XLSX Engine

[snsharma8930]

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;

70

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;

73

74 proc contents data= EXCELP._All_;

75 run;

WARNING: No matching members in directory.

[PGStats]

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

[snsharma8930]

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

[PGStats · Accepted solution]

You must refer to each file separately

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

[snsharma8930]

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.

[Tom]

---

@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.

[Quentin]

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/...

I haven't tried it in more recent versions.

[snsharma8930]

thanks for the response.