topic Re: Read CSv File open by another person in SAS Programming

Read CSv File open by another person

Anotherdream — Wed, 19 Jun 2013 21:49:42 GMT

Hello. In general when you try to input a csv file through an infile statement, if someone has that csv file open, you get the error "The process cannot access the file because it is being used by another process"

This is using Base SAS

windows 7 environment

accessing a file on a shared network drive

Is there a known way around this issue? I have to read this file on a specific timing basis, however it is highly likely that someone will be using this file at the time I need to open it.

Would the only solution be to make a copy of the file using the command line from with SAS, and then read in that copy of the file?

As always, thanks for the help!

Brandon

Re: Read CSv File open by another person

RichardinOz — Thu, 20 Jun 2013 10:43:47 GMT

I think you will find that the other person has the file open in MS Excel, the Windows 'default' application for CSV files. Excel locks any file it has open. Normally text files can be read by multiple users, but Excel locks in case any changes are made.

Talk to your systems admin to see if there is a solution whereby the file can be kept in a folder where only you have write access. You only need read access, but anyone else wanting to read the file in Excel will be forced to make a copy.

Richard

Re: Read CSv File open by another person

Anotherdream — Thu, 20 Jun 2013 14:06:18 GMT

Hi Richard. This is what I had feared. Unfortunately other people need to be able to access the Excel file in question to make changes to the base data with in, which will then be loaded into a database.

Okay, it looks like I will be forced to write the code to make a copy of the file first (this can be done even while locked from Excel, not sure why that is), and then read in the data from the file copy.

Thanks!

Brandon

Re: Read CSv File open by another person

data_null__ — Thu, 20 Jun 2013 14:44:09 GMT

On UNIX there is FILELOCKS system option that would allow SAS to ignore the lock and read the file. I could not find that option documented for Winders. You might try it might work.

Re: Read CSv File open by another person

esjackso — Thu, 20 Jun 2013 14:52:37 GMT

Is it a problem that you might not get the latest data in the copy?

I dont have a lot experience with people actively updating csv files, but I imagine the data in the copy would not have the updated information. Could you move it to Access?

Just a thought!

Re: Read CSv File open by another person

Anotherdream — Thu, 20 Jun 2013 14:55:41 GMT

It is actually not a problem if I do not get the latest data, as the process is set up dynamically to update data as of the grab time (even if someone is working in it, or at least it would be without the locking problem).

Sadly Access is not available as a solution.

Re: Read CSv File open by another person

Anotherdream — Thu, 20 Jun 2013 15:20:52 GMT

So I have done quite a bit of testing, and it turns out as long as you use a call system option, SAS can copy any file regardless of its locked status.

As such, I will simply use the following code, and then use the newly copied file as part of my coding solutions in the future.

OPTIONS NOXWAIT XSYNC;

DATA _NULL_;

CALL SYSTEM ("copy ""G:\Input File Template_6.19.13 v1.csv"" ""G:\Input File Template_6.19.13 v1 test.csv""");

RUN;

This is a 'easy enough' way to get around the problem of file location, while the only cost is the time it takes the command line to create a copy of the given file (not to bad in this instance since the file will only get to 100 MB max).

Thanks for the help everyone!

Re: Read CSv File open by another person

Ce_Wo — Thu, 20 Jun 2013 23:47:43 GMT

Hi,

I have had this problem in the past and the way around it is very similar to the above but using pipes;

filename dirlist PIPE 'copy "\\[SERVER]\[FOLDER]\[FILE].csv" \\[SERVER]\[FOLDER] /y ';

data dirlist;
infile dirlist missover pad;
input Filename $ 1-100 ;
run;

Proc Print data=dirlist;
run;