topic Import a locked excel file in SAS Programming

Import a locked excel file

Ullsokk — Tue, 26 Sep 2017 12:36:27 GMT

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?

Re: Import a locked excel file

Kurt_Bremser — Tue, 26 Sep 2017 12:58:07 GMT

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.

Re: Import a locked excel file

andrewboyd — Tue, 26 Sep 2017 13:00:46 GMT

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.

Re: Import a locked excel file

Ullsokk — Tue, 26 Sep 2017 13:00:48 GMT

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.

Re: Import a locked excel file

Ullsokk — Tue, 26 Sep 2017 13:06:27 GMT

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

Re: Import a locked excel file

AlanC — Tue, 26 Sep 2017 17:46:20 GMT

Write a Powershell script to kill the task.

Re: Import a locked excel file

AlanC — Tue, 26 Sep 2017 18:13:46 GMT

https://stackoverflow.com/questions/24992681/powershell-check-if-a-file-is-locked

Re: Import a locked excel file

error_prone — Tue, 26 Sep 2017 20:32:34 GMT

@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.

Re: Import a locked excel file

AlanC — Tue, 26 Sep 2017 20:43:53 GMT

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