Desktop productivity for business analysts and programmers

Copying Excel & CSV files with more than 66K rows

Reply
Occasional Contributor
Posts: 12

Copying Excel & CSV files with more than 66K rows


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

Grand Advisor
Posts: 10,251

Re: Copying Excel & CSV files with more than 66K rows

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.

Occasional Contributor
Posts: 12

Re: Copying Excel & CSV files with more than 66K rows

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

Grand Advisor
Posts: 10,251

Re: Copying Excel & CSV files with more than 66K rows

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.

Occasional Contributor
Posts: 12

Re: Copying Excel & CSV files with more than 66K rows

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;

Super User
Super User
Posts: 6,383

Re: Copying Excel & CSV files with more than 66K rows

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.

Occasional Contributor
Posts: 12

Re: Copying Excel & CSV files with more than 66K rows

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.

Super User
Super User
Posts: 6,383

Re: Copying Excel & CSV files with more than 66K rows

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.

Occasional Contributor
Posts: 12

Re: Copying Excel & CSV files with more than 66K rows

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.

Grand Advisor
Posts: 17,462

Re: Copying Excel & CSV files with more than 66K rows

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.

Occasional Contributor
Posts: 12

Re: Copying Excel & CSV files with more than 66K rows

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

Thanks, Wil.

Occasional Contributor
Posts: 5

Re: Copying Excel & CSV files with more than 66K rows

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

Occasional Contributor
Posts: 12

Re: Copying Excel & CSV files with more than 66K rows

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

Occasional Contributor
Posts: 5

Re: Copying Excel & CSV files with more than 66K rows

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.

Occasional Contributor
Posts: 12

Re: Copying Excel & CSV files with more than 66K rows

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.

Ask a Question
Discussion stats
  • 21 replies
  • 688 views
  • 0 likes
  • 7 in conversation