Results of file operations - SOLVED!

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
mightycpa
Automation Wizard
Posts: 343
Joined: Mon Jan 12, 2004 4:07 pm
Location: Vienna, VA

Results of file operations - SOLVED!

Post by mightycpa » Tue Mar 28, 2017 9:13 pm

Hi,

I am copying and appending a series of files in a macro, and running the compiled macro on a server.

Code: Select all

COPYFILE>A.FIL, B.FIL
WAIT>5
Because of a variety of things, some of the file copy operations are taking longer than my wait and it's messing me up. I could create a wait, but I'm doing this so many times that the script starts to run into several hours to run. Instead, I think I want to create a loop to monitor the copy until it finishes. I know that two results when finished are FALSE and 0. What is the value BEFORE the copy is finished?

Also, same question for append:

Code: Select all

APPENDFILE>source1.FIL, source2.FIL, target.FIL, apnd_result
WAIT>5
Any suggestions for how to handle this?

Also, while I'm at it, is there a way to figure out if a file is ready to be copied or if it is still being written to?
Last edited by mightycpa on Wed Mar 29, 2017 9:31 pm, edited 1 time in total.
"A facility for quotation covers the absence of original thought." - Lord Peter Wimsey

User avatar
JRL
Automation Wizard
Posts: 3497
Joined: Mon Jan 10, 2005 6:22 pm
Location: Iowa

Re: Results of file operations

Post by JRL » Wed Mar 29, 2017 3:46 am

Doing a WriteLn with no data being written and with WLN_NOCRLF set to 1 (one) does no harm or update to a file. So, for both you can use WriteLn in a loop writing nothing to the file being copied or appended. When the result becomes 0 (zero) the file is no longer locked by the copy or append process and you can move on.

Code: Select all

COPYFILE>A.FIL,B.FIL
Let>wres=1
Let>WLN_NOCRLF=1
While>wres<>0
WriteLn>B.FIL,wres,
Wait>0.01
EndWhile>
Let>WLN_NOCRLF=0
I would have thought, each of these functions copyfile> and appendfile> would pause the script until they completed.

mightycpa
Automation Wizard
Posts: 343
Joined: Mon Jan 12, 2004 4:07 pm
Location: Vienna, VA

Re: Results of file operations

Post by mightycpa » Wed Mar 29, 2017 2:18 pm

Thanks JRL, that does seem to be working a lot better. I've got one last issue, for every file I append, a SPACE is added after every character in the file.

Here's a typical set of calls:

Code: Select all

//datafile to the results   
  CopyFile>v_datafile,v_archive
  Let>wres=1
  Let>WLN_NOCRLF=1
  While>wres<>0
    WriteLn>v_archive,wres,
    Wait>0.01
  EndWhile>
  Let>WLN_NOCRLF=0

//add space for readability to temp file
  AppendFile>v_archive,v_blank_file,%v_archive%_2,v_bytes_written
  Let>wres=1
  Let>WLN_NOCRLF=1
  While>wres<>0
    WriteLn>%v_archive%_2,wres,
    Wait>0.01
  EndWhile>
  Let>WLN_NOCRLF=0

//add the job log to another temp file
  AppendFile>%v_archive%_2,v_job_log,%v_archive%_3,v_bytes_written
  Let>wres=1
  Let>WLN_NOCRLF=1
  While>wres<>0
    WriteLn>%v_archive%_3,wres,
    Wait>0.01
  EndWhile>
  Let>WLN_NOCRLF=0

and so on and so forth until I build the file I want
It's running on Windows 2008 Server R2, but I'm not sure that has anything to do with it. Any ideas?
"A facility for quotation covers the absence of original thought." - Lord Peter Wimsey

User avatar
JRL
Automation Wizard
Posts: 3497
Joined: Mon Jan 10, 2005 6:22 pm
Location: Iowa

Re: Results of file operations

Post by JRL » Wed Mar 29, 2017 2:44 pm

UNICODE? or some variation of it?

mightycpa
Automation Wizard
Posts: 343
Joined: Mon Jan 12, 2004 4:07 pm
Location: Vienna, VA

Re: Results of file operations

Post by mightycpa » Wed Mar 29, 2017 2:46 pm

checking, stand by... and thanks
"A facility for quotation covers the absence of original thought." - Lord Peter Wimsey

mightycpa
Automation Wizard
Posts: 343
Joined: Mon Jan 12, 2004 4:07 pm
Location: Vienna, VA

Re: Results of file operations

Post by mightycpa » Wed Mar 29, 2017 3:13 pm

The file that appears to be causing the problems is encoded ANSI, according to Notepad++
But, if I look at the file before MS gets a hold of it, the file is encoded UCS-2 LE BOM
The file is originally generated by SQL Server as a job log.

Not sure what ANSI means in this context, but check this out:

Image

This is the file after it is touched by MS. Before MS gets involved, it doesn't have all those nulls. In Notepad, the pictured file renders as if a space was between each char, sublimetext renders it correctly and that picture above is from Notepad++

So it must be happening on the copy or append. I can nail it down a little better after this post.
"A facility for quotation covers the absence of original thought." - Lord Peter Wimsey

mightycpa
Automation Wizard
Posts: 343
Joined: Mon Jan 12, 2004 4:07 pm
Location: Vienna, VA

Re: Results of file operations

Post by mightycpa » Wed Mar 29, 2017 3:50 pm

I changed my code so that after the SQL Server job is finished, I copy the log to where I want to put the results. Both before and after the copy, I used your loop to
1) make sure the source file was ready
2) make sure the copied file was completely copied

Code: Select all

//make sure the job log is ready
  Let>wres=1
  Let>WLN_NOCRLF=1
  While>wres<>0
    WriteLn>v_job_log,wres,
    Wait>0.01
  EndWhile>
  Let>WLN_NOCRLF=0
//now copy it to the results folder
  CopyFile>v_job_log,v_job_log_copy
  Let>wres=1
  Let>WLN_NOCRLF=1
  While>wres<>0
    WriteLn>v_job_log_copy,wres,
    Wait>0.01
  EndWhile>
  Let>WLN_NOCRLF=0
then I do the rest, and rather than delete the temp files, I kept them so that I could see where the encoding was introduced. The job log is fine. The copied job log is fine. The encoding gets introduced when the job log is appended to the data file. It begins at step 4 below, and everything below it has the same problem.

The process is basically this:

1) copy the job log to the results folder
2) copy the data file to the results folder
3) append a blank space text file to the data file
4) append the job log to the data file

Code: Select all

 Let>WLN_NOCRLF=0
  
//add the job log to another temp file
  AppendFile>%v_archive%_2,v_job_log_copy,%v_archive%_3,v_bytes_written
  Let>wres=1
  Let>WLN_NOCRLF=1
  While>wres<>0
    WriteLn>%v_archive%_3,wres,
    Wait>0.01
  EndWhile>
  Let>WLN_NOCRLF=0
5) append job done file to the data file
6) append job evaluation to the data file

Finally, this is what I see when I look at it. You can see the character that's been introduced by the append. Notice how the line above doesn't have the spaces. That's the data file + blank file. Oh, and yes, I did look in the blank text file to make sure it wasn't added at the end.

Image
"A facility for quotation covers the absence of original thought." - Lord Peter Wimsey

mightycpa
Automation Wizard
Posts: 343
Joined: Mon Jan 12, 2004 4:07 pm
Location: Vienna, VA

Re: Results of file operations

Post by mightycpa » Wed Mar 29, 2017 9:30 pm

OK, so a couple of insights...

First, as to the original question, "how can you tell if a file is open?" I found this:

https://technet.microsoft.com/en-us/sys ... andle.aspx

Using that, you can find the file handle of the application using a file. If there is none found, you know the file is ready. This might be more useful than, or can be used in conjunction with JRL's technique if you're interacting with a file that is periodically being updated by some other program. Otherwise, JRL's solution works very well.

Second, on Windows Server, the files I was using were being created by a variety of programs, and so each had different encoding formats which conflicted with each other. In the end, I had to read and re-write some files that didn't play well with the others, and also combine files based on what program wrote them using the COPY command, then I used the TYPE command and double piping symbol to append some files to other files, and using a combination of these three techniques gave me exactly what I wanted.

this is how you use the two dos commands:

Code: Select all

//to copy two files into a third one
Run>cmd.exe /c copy /y /a source_file_1 + source_file_2 combined_file

//to append the contents of one file onto a second one ( > overwrites files, >> appends)
Run>cmd.exe /c type file_1 >> file_2
"A facility for quotation covers the absence of original thought." - Lord Peter Wimsey

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts