Import Excel file into SAS

[Ronein]

Hello

I am using SAS Enterprise.

I am trying to import  Excel file (Xlsx) into SAS enterprise.

The excel File contain only one spreedsheet .

The columns headers of excel file are not in Egnlish and the user who created the excel file cannot change it to English.

I use import wizard to import the excel file and SAS print the code in the lof window.

I took the code and changed the columns names to English and all is fine and suceesfully imported the excel file.

The problem is that when I use the code again (Next day or when I close SAS and open SAS again)  then it won't work.

The error is "physical file does not exist"

It is a big problem because my client should Run the code in daily base and just upload a new excel file with same name everyday.

Can you please suggest a solution?

Thanks

[Norman21]

Some things you might like to try have been discussed here:

https://communities.sas.com/t5/General-SAS-Programming/ERROR-Physical-file-does-not-exist/td-p/11200...

[Kurt_Bremser]

If you used the import wizard in a client-server environment, then the whole process has two stages: EG converts the Excel to text and uploads that, and then executes code that imports the text to a dataset. No stage works without the other.

[Reeza]

---

@Ronein wrote:

Hello

I am using SAS Enterprise.

I am trying to import  Excel file (Xlsx) into SAS enterprise.

The excel File contain only one spreedsheet .

The columns headers of excel file are not in Egnlish and the user who created the excel file cannot change it to English.

I use import wizard to import the excel file and SAS print the code in the lof window.

I took the code and changed the columns names to English and all is fine and suceesfully imported the excel file.

The problem is that when I use the code again (Next day or when I close SAS and open SAS again)  then it won't work.

The error is "physical file does not exist"

It is a big problem because my client should Run the code in daily base and just upload a new excel file with same name everyday.

Can you please suggest a solution?

Thanks

---

Does the code work the same day with the same file?

If not, then the code isn't valid for some reason. Assuming 'Enterprise' is SAS EG then the file likely needs to be on the server so you likely have the path incorrect. You can move the file to the server or set up a shared folder where your client can drop the file and SAS EG can access the file. Talk to your IT administrator on how to set this up.

[Ronein]

The excel file as same name every day and just the data is different every day.
The client will upload every day a new file.
The excel file is on a shared drive .
I didnt understand how IT admin can help me?
Please explain what action is required?

[Ronein]

Thank you but how your information vam help me to solve the problem?
What action is required that the client will the import code in daily base without error that phsical file doesn't exist

[Kurt_Bremser]

---

@Ronein wrote:
Thank you but how your information vam help me to solve the problem?
What action is required that the client will the import code in daily base without error that phsical file doesn't exist

---

If the file resides on a drive that the SAS server can see, and only a portion of the filename changes each day, you can do the following:

- use the code that you have that works for the current file

- create a user prompt for the variable part of the filename

- use the result of the user prompt in the filename. User prompts are supplied to the code as macro variables, with the name of the prompt

[Kurt_Bremser]

---

@Ronein wrote:
Thank you but how your information vam help me to solve the problem?
What action is required that the client will the import code in daily base without error that phsical file doesn't exist

---

But if you imported the file with the import wizard, my earlier post is valid, and the code from that won't work on its own without the conversion/file transfer that EG does. For that to work, the customer must also use EG, and you have to give them the project, and not just the code.

Consider writing code that uses proc import directly on the Excel file, if the SAS server can "see" the shared location.