topic Re: Reading all worksheets from XLS file in SAS Procedures

Reading all worksheets from XLS file

Ramesh_165 — Mon, 24 Jun 2019 18:12:42 GMT

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

Re: Reading all worksheets from XLS file

Reeza — Mon, 24 Jun 2019 18:34:22 GMT

LIBNAME with XLSX.

Re: Reading all worksheets from XLS file

koyelghosh — Mon, 24 Jun 2019 18:38:45 GMT

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.

Re: Reading all worksheets from XLS file

Reeza — Mon, 24 Jun 2019 18:43:33 GMT

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

Re: Reading all worksheets from XLS file

Ramesh_165 — Mon, 24 Jun 2019 18:59:32 GMT

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

Re: Reading all worksheets from XLS file

Ramesh_165 — Mon, 24 Jun 2019 19:01:59 GMT

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

Re: Reading all worksheets from XLS file

Reeza — Mon, 24 Jun 2019 19:05:22 GMT

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?

Re: Reading all worksheets from XLS file

Ramesh_165 — Mon, 24 Jun 2019 22:38:18 GMT

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

Re: Reading all worksheets from XLS file

Reeza — Mon, 24 Jun 2019 22:41:11 GMT

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

Re: Reading all worksheets from XLS file

Ramesh_165 — Sat, 29 Jun 2019 07:08:00 GMT

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.

Re: Reading all worksheets from XLS file

Reeza — Sat, 29 Jun 2019 18:49:45 GMT

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.

Re: Reading all worksheets from XLS file

SASKiwi — Sat, 29 Jun 2019 22:45:54 GMT

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&locale=en

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.

Re: Reading all worksheets from XLS file

Tom — Sun, 30 Jun 2019 15:12:06 GMT

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