topic Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened in SAS Programming

Libname method using PCFiles engine for Excel creates Excel files that can't be opened

BBSHOPSINGER — Sat, 19 Nov 2016 00:11:13 GMT

I'm using SAS 9.4 and trying to use code that worked fine in 9.3 but is giving me problems.

I'm using the libname method to export data into Excel. The original libname statement was

libname xls "V:\<spreadsheet name>.xlsx" (<spreadsheet name> is the full address of the file)

I changed the code to add in pcfiles path= so it looks like this:

libname xls pcfiles path="<spreadsheet name>.xlsx"

The code runs fine, and everything appears to work. But when I try to open the resulting excel file, Excel will not open. It isn't the libname clear issue, but Excel is telling me that the file is in an invalid format. Any ideas?

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

Reeza — Sat, 19 Nov 2016 01:00:47 GMT

Try using XLSX and not PCFILES.

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

GreggB — Sat, 19 Nov 2016 16:25:13 GMT

Take out the "xls".

Instead: libname pcfiles path="<spreadsheet name>.xlsx"

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

Patrick — Sun, 20 Nov 2016 01:18:14 GMT

I'm with @Reeza

If you're using a current SAS release then use the XLSX enging and not PCFILES

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n0oj9f6i838mymn148890ckla700.htm

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

BBSHOPSINGER — Mon, 21 Nov 2016 16:27:19 GMT

Thanks to both of you for your ideas. I actually tried both to no avail. Unfortunately I don't have the latest build of 9.4, so the attempt at using the xlsx method failed. I tried removing the libname (xls) and just using "pcfiles" as my libname reference, and this didn't work either. I tried:

libname pcfiles path=...., but this only gave me a message saying there is no such libname.

I tried again changing the libname to xlsx:

libname xlsx pcfiles path=....

This again appears to work, with no errors in SAS, but the resulting Excel file won't open. The error states, "Excel cannot open the file '<filename>.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

Any other ideas?

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

GreggB — Mon, 21 Nov 2016 16:45:25 GMT

You need a libref.

libname MYDATA pcfiles path='....';

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

Reeza — Mon, 21 Nov 2016 18:02:46 GMT

@BBSHOPSINGER You're putting too much into your libname statement. It's either PCFILES or XLSX not both.

libname out XLSX 'path to my file';

This worked as of SAS 9.3 so even if you don't have the latest version of SAS 9.4 it should work.

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

BBSHOPSINGER — Mon, 21 Nov 2016 19:23:47 GMT

My "xlsx" was intended to be the libname. I experimented with several libnames with the same result. No errors in the log, it appears to work fine, but the resulting Excel file will not open when using the pcfiles code.

When I use the xlsx method, it also appears to work, but again I get unreadable content in the excel file. The message here is "Excel found unreadable content in <filename>. Do you want to recover the contents of this workbook?" If I click yes, it opens, but with unreadable content.

If I try to use the method that worked fine in 9.3 (just using a libname statement without an "xlsx" or "pcfiles") it gives me a SAS error:

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

I think I'm going to have to find a different method here. Could the issue be 64-bit SAS vs. 32-bit Office?