DATA Step, Macro, Functions and more

Import a locked excel file

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Import a locked excel file

I have a batch that will read data from an excel file on a daily basis, and run the programs whose filepath is included in the file. This will make it easier to add new programs to the batch, and keep track of which programs have a batch job. 

 

Few people have access to the excel-file, and getting a lock would be quite rare, as the batch runs outside of working hours. Still, I was wondering if there is a way to read the file even if the excel file is opened by someone. There doesn't seem to be a sas-spesific way to do this, but I can sort of imagine how to write this as a shell script that either runs is the file has a lock, as a X-command, or just writing a standalone shellscprit that either forces a close or makes a copy, so that the batch can import the file with the contents of the previously saved file. 

 

Is there a standard best practice for such situations? 


Accepted Solutions
Solution
‎10-05-2017 05:37 AM
Contributor
Posts: 24

Re: Import a locked excel file

Posted in reply to andrewboyd

On second thought, I might as well just make a copy and read that file instead. No need to run a condition beforehand

View solution in original post


All Replies
Super User
Posts: 10,571

Re: Import a locked excel file

Don't keep such control data in a spreadsheet file, use a text file instead, and edit it with a text editor.

Notepad++ or the standard Windows Editor (just to give some examples) do not lock the file.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 24

Re: Import a locked excel file

Posted in reply to KurtBremser

That will probably not be an option in my case. In any case, I am planning to use a x-command to create a temporary copy of the file if it is already open. The only problem is getting SAS to check if the file is already open.

Regular Contributor
Posts: 150

Re: Import a locked excel file

New Contributor
Posts: 4

Re: Import a locked excel file

Have you tried testing this to see what happens? I ask because as you aren't writing back to the excel file I can't see why there would be an issue as sas would presumably just get a read only version of the file just as a user would when they open a worksheet already opened by another user.

Solution
‎10-05-2017 05:37 AM
Contributor
Posts: 24

Re: Import a locked excel file

Posted in reply to andrewboyd

On second thought, I might as well just make a copy and read that file instead. No need to run a condition beforehand

Regular Contributor
Posts: 150

Re: Import a locked excel file

Write a Powershell script to kill the task.

Regular Contributor
Posts: 226

Re: Import a locked excel file


AlanC wrote:

Write a Powershell script to kill the task.


Hardly possible to do in a Client-Server-Setup, the task locking the Excel-file will most likely not be running on the same machine as the sas process.

Regular Contributor
Posts: 150

Re: Import a locked excel file

Posted in reply to error_prone

Powershell can be executed remotely. However, I don't think we have enough informationas to the configuration of the system in question here.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 524 views
  • 2 likes
  • 5 in conversation