PROC IMPORT and xls file

[GreggB]

This code worked fine until recently:

proc import datafile="G:fakepath\&year.\Preliminary\3701_Prelim_Score_Roster - 14JAN19.xls"
out=eoc dbms=excelcs replace;
run;

Now, I get this error:

ERROR: Failed to connect to the Server: .
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.10 seconds
      cpu time            0.03 seconds

I wondered if my latest SAS renewal was the issue. Here's what I'm running on a Windows 64bit desktop:

Product(s) Ordered		Expiration Date
SAS Analytics Pro, including the products:
• Base SAS		November 29, 2020
• SAS/GRAPH		November 29, 2020
• SAS/STAT		November 29, 2020
SAS Enterprise Guide		November 29, 2020
SAS/ACCESS Interface to ODBC		October 30, 2019
SAS/ACCESS Interface to PC Files		November 29, 2020

[JeanDo]

Hello,

Is your running-code environment weel-connected to your local windows? Are you working with SAS Studio or SAS EG?

Regards,

JD

[GreggB]

I'm running Base SAS 9.4

[Reeza]

Is your PC Files Server running? I would recommend trying dbms=excel for an excel file. Otherwise, the next step would be to see if the PC FILE Server is installed, launch it and then try the code again.

[GreggB]

I changed  my code to dbms=excel and got a different error.

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.20 seconds
cpu time 0.07 seconds

[Tom]

Why not try using DBMS=XLS and avoid trying to depend on getting SAS to talk to Windows/Microsoft code?

[GreggB]

When I do that it reads column 1 only (in a weird fashion)  like so:

should have:

3701551

instead:

3 7 0 1

[Tom]

What is producing the XLS file?  Are they really old?  If not then perhaps your process can produce the more modern (and better documented) XLSX format instead?

[GreggB]

the xls file comes from an outside vendor. not sure why they won't switch over to xlsx format

[Reeza · Accepted solution]

Worse case scenario, this VBS code will convert an XLSX to CSV, and you could modify it to convert an XLS to CSV or XLSX and see if either of those formats worked.

https://gist.github.com/statgeek/878e585102c14e01581f55dbe972d27e

[JeanDo]

Hi,
Dis toi try this method :

libname test pcfiles path=your-path-to-the-file;

data toto;
set test.sheet1;
run;

When sheet1 is the name of the sheet you want to ingest

[Reeza]

If ExcelCS doesn't work, PCFILES won't work.
@GreggB, did you try turning on your PCFILE server and seeing if it works? It is an application you have to start if you're not working on a SAS server at an organization where it's managed by someone else.