Unable to import Excel file - Two slashes error //

[ThunderOne]

Unable to import an excel file through EG... here is the code (removed various parts for security).  

The log shows an additional slash being added... not sure how to fix (SAS EG 7.13):

%Let Upload = /sasdata/users/jondoe1/;
%Let Path_In = \\Client\S$S:\DA_work_files\filename.xlsx;

/*Run Copy Task to Copy to Out network*/

Proc Import Out=jondoe1_Lib.list

Datafile='//sasdata/users/jondoe1/filename.xlsx'

dbms=xlsx replace;

Sheet='List';

Run;

The macros run fine. 

Log:

ERROR: Physical file does not exist, //sasdata/users/jondoe1//filename.xlsx.

[Reeza]

The extra slashes aren't the issue. Is your XLSX file on the server?

[ThunderOne]

I am not sure, as this is a colleague's work.  I was assuming the first statement would bring the file into the sasusers folder.  I ended up using the import wizard and importing the file directly, rather than making it two steps.  That worked.

Thank you for your help.

[Reeza]

Yeah, I think you're running into the 'server' vs 'desktop' issue. the file is on your local drive and you can't access it until its on the server. The GUI doesn't have the same restriction. 

Yes, this is super annoying.

[Patrick]

@Reeza

Actually the two slashes at the beginning of the path indicate that this is a UNC path so depending on the site specific topology such a path could work from both client and server side.

//sasdata/users

Not sure why you believe the two slashes in the middle of the pathname aren't an issue. I believe they are.

 //sasdata/users/jondoe1//filename.xlsx

@ThunderOne

The code you've posted doesn't show it but eventually remove the last slash in below command line.

%Let Upload = /sasdata/users/jondoe1/;

[Reeza]

@Patrick I don't know why it's not an issue, but I know that it is. SAS frequently does this and if you include 2 it works fine as well. See code and log below.

I *think* it might be the local vs server issue because in the middle of the path is S:\ -> Looks like a drive mapping, but I could definitely be wrong there. 

%Let Upload = /sasdata/users/jondoe1/;
%Let Path_In = \\Client\S$S:\DA_work_files\filename.xlsx;

Code:

proc import out=want datafile='/folders/myfolders//output.csv' dbms=csv replace;
run;

60 /**********************************************************************

61 * PRODUCT: SAS

62 * VERSION: 9.4

63 * CREATOR: External File Interface

64 * DATE: 24AUG17

65 * DESC: Generated SAS Datastep Code

66 * TEMPLATE SOURCE: (None Specified.)

67 ***********************************************************************/

68 data WORK.WANT ;

69 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

70 infile '/folders/myfolders//output.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

71 informat _____Alfred $7. ;

72 informat M $1. ;

73 informat _14 best32. ;

74 informat VAR4 $1. ;

75 informat _112_5 best32. ;

76 informat VAR6 $1. ;

77 informat _69 best32. ;

78 format _____Alfred $7. ;

79 format M $1. ;

80 format _14 best12. ;

81 format VAR4 $1. ;

82 format _112_5 best12. ;

83 format VAR6 $1. ;

84 format _69 best12. ;

85 input

86 _____Alfred $

87 M $

88 _14

89 VAR4 $

90 _112_5

91 VAR6 $

92 _69

93 ;

94 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

95 run;

NOTE: The infile '/folders/myfolders//output.csv' is:

Filename=/folders/myfolders//output.csv,

Owner Name=UNKNOWN,Group Name=games,

Access Permission=-rw-r--r--,

Last Modified=June 21, 2017 21:27:47 o'clock,

File Size (bytes)=1440

NOTE: 18 records were read from the infile '/folders/myfolders//output.csv'.

The minimum record length was 73.

The maximum record length was 77.

NOTE: The data set WORK.WANT has 18 observations and 7 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

[DazyParker]

ERROR: Physical file does not exist.

Reasons:

• Path of the file is mispelled
• Directory and subdirectory is missing
• Permission issue
• Server issue
• You do not follow the nomenclature of the file name
• drive is not mapped into correct location

[Bilbottom]

I just encountered this double-slash issue myself, and the root of the problem was that the file to import was open. Once I closed the file, the proc import worked fine.

[DGBK]

Thank you. I was having a similar problem today. I did not get an error that the file was not imported, but I was getting the double slash. This should be listed as the answer.

[Tom]

The extra / in the error message is a BUG in the error report code of PROC IMPORT.  It does that for XLSX engine, but not for the CSV engine.

Example:

proc import datafile='~/nosuchfile' out=want dbms=xlsx;
run;
proc import datafile='~/nosuchfile' out=want dbms=csv;
run;

Results:

 69         proc import datafile='~/nosuchfile' out=want dbms=xlsx;
 70         run;
 
 ERROR: Physical file does not exist, /home/user1//nosuchfile.


  
 71         proc import datafile='~/nosuchfile' out=want dbms=csv;
 72         run;
 
 ERROR: Physical file does not exist, /home/user1/nosuchfile.