Reading all worksheets from XLS file

[Ramesh_165]

I'd like to read all the worksheets from a .xls file. 

1. I tried LIBNAME with EXCEL engine. But it gave me the following error.

     ERROR: The EXCEL engine cannot be found.
     ERROR: Error in the LIBNAME statement.

2. I tried LIBNAME with PCFILES opotion. I got the below error.

     ERROR: A socket routine returned error: The connection was refused.
     ERROR: Unable to connect to server.
     ERROR: Error in the LIBNAME statement.

3. PROC IMPORT procedure needs the sheet names beforehand to loop through and get the sheets as datasets. 

So any help to overcome the problem to get the solution will be greatly appreciated. Thanks a lot in advance!!

Ramesh

[Reeza]

LIBNAME with XLSX.

[Ramesh_165]

I'm trying to read XLS file not XLSX.

Libname with XLSX to read an XLS file, creates a library but doesn't have any datasets/sheets inside the library.

Thanks,

Ramesh

[Reeza]

It doesn't always show them...I think that's a bug unfortunately. What happens if you run a Proc datasets on that library though? I would consider mass converting them to XLSX (I have a VB script for that if needed) and then importing but that may not be an option. I'm assuming this is a one-time conversion?

[Ramesh_165]

PROC DATASET shows empty list for that library. 

We are trying to compare an XLSX to XLS file and produce some sort of report. It is really on-demand, and the XLS files are spreadout in different folder structures. Also they are old proprietary files. So we want to convert them all to XLSX files in the extreme worst case. 

if we are able to read atleast the sheet names from the XLS files, we can do the proc import in loop. 

Thanks for spending your valuable time.

Ramesh

[Reeza]

Then I'd suggest going back to PCFILES. Post the code you used and make sure the PC FILES SERVER is running.

[Ramesh_165]

Thank you!

LIBNAME with PCFILES worked after getting the proper PCFILE server name and PORT number from our admin. 

But the PCFILES option is working only for the excel files in the windows server. 

Most of our files are unix server, and for which PCFILE option doesn't seem to be working. 

Please let me know if you have any recommendation/suggestions for the xls files in unix server. 

[Reeza]

Unfortunately I don't think there's a method on Unix Server, xls files are pretty old, Excel changed its file formats over a decade ago (2007) to xlsx.

[koyelghosh]

I am sorry I am not aware if you can do it in a single step. There is a discussion here (https://communities.sas.com/t5/SAS-Procedures/PROC-IMPORT-multiple-sheets/td-p/149597). What you basically want to do is make a macro and call with different sheet names. If there are too many sheet names to manually enter then before the macro you may want to use VBA on your excel file to extract all sheet names and store it some where and then in a for loop you may call the macro with each sheet name. This is definitely more work if you have many many sheets. If the number of sheets are less you may just call the macro for a simpler solution.

[Reeza]

A response/solution from 2013 may not be relevant today.

[Ramesh_165]

Yes I have close to 100 XLS workbook, and each workbook atleast has couple hundred worksheets. So looking for more of an automatic solution.

Thanks,

Ramesh

[SASKiwi]

What version and maintenance level of SAS are you using, and on what operating system? SAS/ACCESS to PC Files has changed significantly in recent maintenance releases: https://documentation.sas.com/?docsetId=acpcref&docsetTarget=acpcrefwhatsnew94.htm&docsetVersion=9.4...

Please also post your test code. Did you try the XLSX LIBNAME engine like: libname MyXLSX XLSX 'MyXLSWorkbook'; If that doesn't work correctly, try converting one of your workbooks to XLSX format (FILE SAVE AS) and see if that works OK.

With all due respect anyone who designs processes using nearly 100 Excel workbooks each with 200 worksheets should consider much better ways of doing this.

[Tom]

---

@Ramesh_165 wrote:

Yes I have close to 100 XLS workbook, and each workbook atleast has couple hundred worksheets. So looking for more of an automatic solution.

Thanks,

Ramesh

---

Do the names of the sheets in the XLS files follow a pattern?  If so then you should be able to write a program to read all of the sheet by using the pattern to generate the sheet names.  If you have a pattern (or think there might be a pattern) but cannot figure out how to make the code post enough information to explain the pattern and you can get some help creating the code.