ERROR: Physical file does not exist while importing Excel file

[dblkickd2]

Hello,

Current student learning SAS and neither I nor my instructor has found a solution. I apologize now if it seems elementary to most. I am trying to import an Excel file into SAS University and continue to get these errors. If anyone can shed some light, I'd appreciate any help! I noticed that the error does not include the .xlsx at the end of the statement. Thank you.

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

73 /* Generated Code (IMPORT) */

74 /* Source File: NWdw.xlsx */

75 /* Source Path: /folders/myfolders/sasuser.v94 */

76 /* Code generated on: 11/25/18, 8:42 AM */

77

78 %web_drop_table(WORK.IMPORT);

79

80

81 FILENAME REFFILE '/folders/myfolders/sasuser.v94/NWdw.xlsx';

82

83 PROC IMPORT DATAFILE=REFFILE

84 DBMS=XLSX

85 OUT=WORK.IMPORT;

86 GETNAMES=YES;

87 RUN;

ERROR: Physical file does not exist, /folders/myfolders/sasuser.v94//NWdw.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

88

89 PROC CONTENTS DATA=WORK.IMPORT; RUN;

ERROR: File WORK.IMPORT.DATA does not exist.

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.

90

91

92 %web_open_table(WORK.IMPORT);

93

94 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

107

[Cynthia_sas]

HI:

  Can you find the XLSX file in your Files and Folders list? If so, right click on the file name and select Properties. In the Location area of the Properties window, you should see the fully qualified path of the XLSX file. That is the path you need to use.

  Your ERROR message is telling you that your path name is wrong -- either you've misspelled the file name or something in the path, you've got the case wrong (uppercase instead of mixed or lower case) or the file does not exist in the physical path you've specified.

Cynthia

[dblkickd2]

Hi Cynthia,

I tried your suggestion but it is still producing the same error. I have the correct path but then it adds this to the beginning if the statement:

/opt/sasinside/SASConfig/Lev1/SASApp/  which is not part of the path and it is still cutting off the file ext. It also adds and extra / before the filename as well? I also changed the filename to lowercase to stay consistent. 

ERROR: Physical file does not exist,

/opt/sasinside/SASConfig/Lev1/SASApp/C:\Users\dblki\Desktop\School\MIS450\SASUniversityEdition\myfolders\sasuser.v94\/nwdw.

[Cynthia_sas]

Hi:
I did not mean to use the Windows name in your code. I meant to look in the Files and Folders list in SAS Studio. With SAS University Edition, you cannot use a C: drive location. You MUST define your shared folders correctly. Then, you still have to use the /folders/myfolders reference. For example, in my SAS University Edition shared folder location, I have a folder called all_excel, where I put my Excel sheets. So then I need to get the properties by finding the file INSIDE SAS Studio and then right clicking on the file name and selecting Properties, as shown below:

Next I need to copy the fully qualified path, including the /folders/myfolders part of the name, as shown below:

Note how the Location field shows the full name: /folders/myfolders/all_excel/custfm.xls -- this is the path I would use in code. You cannot use a Windows path location in your code with University Edition.

Cynthia

[dblkickd2]

Hi,

Thank you for the suggestion but that is not working either?

[Cynthia_sas · Accepted solution]

Hi:
/folders/myfolders/all_excel was MY folder structure. It will NOT work for you unless you
1) made a folder called all_excel and then
2) moved the nwdw.xlsx file to THAT particular folder.

It's not clear to me whether you did those 2 things because you did not show the SAS Studio screen with the Files and Folders pane so we could see your actual folder path.

Since you do NOT show your Files and Folders location, it is impossible to tell what the right path should be.

Can you show us the Server Files and folders pane on YOUR copy of University Edition that shows the nwdw.xlsx properties?

In my screen shot, I right clicked on the custfm.xls file and chose Properties from the popup window in the SAS Studio Files and Folders pane. My Excel file was stored in my shared folder location under the all_excel folder. Did you make an all_excel folder? Did you put your XLSX file in THAT location. If so, please show the Properties shot for nwdw.xlsx like I showed for custfm.xls in my previous post.

Showing us your error message is not useful. I can generate the same error message in 2 different ways:
1 - mis-spelling one of the folder names (such as all_excl instead of all_excel)

or
2 - using a folder name like "wombat" that does not exist at all

Cynthia

[dblkickd2]

Hi Cynthia,

My apologize on the inadequate screenshot. Yes as seen below, I followed your advice and steps for creating the all_excel folder and moved my file to it. In the meantime, I decided to take a chance and changed the extention to an older version .xls file. When I ran it again, it said to save in an Excel format ver. 5 or later. So I took a chance and saved it as a .csv file and it worked! I'm not sure how but it may have something to do with the ..xlsx and .xls file ext that SAS did not like? Maybe it was the way that Pentaho created the XML file? Not sure but would like to find an answer because that was strange! Thank you so very much for your help!!