Copying Excel & CSV files with more than 66K rows

[WilBeach]

Hi,

I have a CSV file with 250K rows. How can I copy this file from one folder to another. This is the code I am using:

OPTION NOXWAIT;

DATA RC;

     RC = SYSTEM ('copy "G:\Data Files\LAST_MODIFY_DATE_21JUL15_test.CSV" "E:\sasuserfolders\WBEACH\*.*"');

RUN;

I get a Return Code of 0 but the copy did not work because the CSV file has more than 66K rows. When I test the same code on a file with less than 65K row it works.

Thanks,

Wil Beach

[ballardw]

The system copy command should not be file size dependent. In you test with the smaller set did you use a target file name of  *.*? I would expect issues with that.

Was the result no file at all, a file but was empty or a file with fewer rows of data than the original? Or something else as "did not work" isn't a very complete description.

Also, were you reusing a target file name?

by any chance have you reversed the source and destination names? Windows Copy wants the source first then the destination.

[WilBeach]

The code works for a smaller file. When using a large file I get a return code of 0 but no file was copied.

[ballardw]

1) Did you reuse a destination name? The default behavior for Copy is to prompt for replace. Since you're not going to get that prompt then that could be the problem. add the /Y switch to test if that may be the case.

[WilBeach]

How should I revise the code below:

OPTION NOXWAIT;

DATA RC;

     RC = SYSTEM ('copy "G:\Data Files\LAST_MODIFY_DATE_21JUL15_test.CSV" "E:\sasuserfolders\WBEACH\*.*"');

RUN;

[Tom]

The statement syntax is looks wrong, but apparently Windows supports it.  Personally I would remove the *.* from the destination if I wanted to copy the file using the same name it had in the source directory or specify a full name if I wanted to copy it another name.

You can use a PIPE instead of SYSTEM or X or %SYSEXEC to see the error messages from the command.

%let fname=c:\downloads\class.xls;

%let out=c:\downloads\nosuchdir\;

DATA RC;

  infile "copy  ""&fname"" ""&out"" " pipe;

  input ;

  put _infile_;

RUN;

        0 file(s) copied.

Stderr output:

The system cannot find the path specified.

[WilBeach]

I am running this on a SAS Server and I get an "Access is denied" message (see below). Is there something I can set on the SAS Server to resolve this.

        0 file(s) copied.

Stderr output:

Access is denied.

NOTE: 1 record was read from the infile "copy  ""G:\Data Files\LAST_MODIFY_DATE_23JUL15.CSV""

      ""\\rgsss01\marketing$\Monica\James Emails\"" ".

      The minimum record length was 25.

      The maximum record length was 25.

NOTE: The data set WORK.RC has 1 observations and 0 variables.

[Tom]

It probably means that you do not have permission to write to that folder. It might mean that the file is already there and someone has it open so Windows is not letting you overwrite it.

[WilBeach]

When I run this same code in Enterprise Guide it works. When I run on SAS Server I get "Access Denied". Is there something on the SAS Server I can set to resolve this.

Thanks.

[Reeza]

I think your server can't see your E drive. Try a different drive.

You'll need to set up your server to either see your E drive or write it to a different location.

[WilBeach]

Any SAS Server Admin out there can help me set up the SAS Server to access any folder on our server?

Thanks, Wil.

[ArthurWilliams]

Try replacing the drive reference "E:sasuserfolders\wbeach\*.*" with the UNC path \\machinename\sasuserfolders\WBEACH\*.*  where "machinename' is the name of the computer that the E: drive is associated.

on the Windows server launch the "Computer Manager" and look for the "Storage" section on the left, expand "Storage" and click on "Disk Management" this will list all of the disks on the server.  If the E: drive is not listed then it is not attached to the server and you will need to use the UNC or Map the drive (if you map the drive it will only be available to your ID/profile and is not a global map.).

[WilBeach]

Arthur, thanks for all your help. Here is my production code:

%let fname=G:\Data Files\LAST_MODIFY_DATE_&SYSDATE..CSV;

%let out=\\rgsss01\marketing$\Monica\James Emails\;

DATA RC;

      infile "copy ""&fname"" ""&out""" pipe;

      input ;

  put _infile_;

RUN;

I get an "Access Denied" on \\rgsss01\marketing$\Monica\James Emails\

The attached file displays what I have access to using Explorer & Disk Management. To me it looks like I have access to everything.

Thanks again,

Wil

[ArthurWilliams]

Wil, it looks like you do not have permissions to the directory, are you able to navigate using Windows Explorer to \\rgsss01\marketing$\Monica\James Emails\ if you are not able to then you will need to be granted permissions to the directory.

FYI there is no attachment, try inserting an image.

[WilBeach]

My attachment was too large and I can't Copy & Paste what is in the attachment. On the SAS Server the folder "\\rgsss01\marketing$\Monica\James Emails\" shows up in Explorer, but it doesn't on Disk Management. What is confusing is I have a job like this that works okay using a MDB file or CSV or Excel files with less than 65K rows but it will not work for a CSV or Excel files with more than 66K rows. When I run the program in EG it works with large files but not on the SAS Server. Maybe it is a 32-bit or 64-bit issue.