Batch File to delete and move files at the end of the month

Last year Bill Prew (bp) wrote this batch file for me--see attached text file. What it does is allow me to designate how many files I want to keep in a designated folder (set to 30). It then also checks the files in those folders and moves any that have an "end of the month" date to an EOM folder. Works great for the files with a name-date format _201109302145.BAK. I tried to use the solution given and apply it to other files with different name-date formats and for the life of me I can't get it to work. So please help me adjust this batch file to apply to these two name-date files please "Central-QBE-20140330.QBW" and
"HappinessFarms v2014-03-28.QBW". Thank you.
EOM-Org.txt
LVL 26
Lionel MMSmall Business IT ConsultantAsked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
Here's an example modification that makes it easier to adjust the location of the date parts in the file name.  I've provided some examples in the call to :DateParts for the different format file names you have.

If the script needs to handle all three formats in the same run then we can adjust for that, just waiting to hear...

@echo off
setlocal EnableDelayedExpansion

rem *!* Process each folder
call :ProcessDir "Y:\SQL-E\SQL-Backup\Databases\Dynamics" "Y:\SQL-E\SQL-Backup\Month Ends\Dynamics" "*.bak" 30
call :ProcessDir "Y:\SQL-E\SQL-Backup\Databases\HFI" "Y:\SQL-E\SQL-Backup\Month Ends\HFI" "*.bak" 30

rem *!* Done, exit
goto :EOF

rem *!* Process a folder, saving month end files, and keeping only desired number of files
:ProcessDir [base-dir] [save-dir] [filter] [files-to=keep]
  setlocal
  pushd "%~1"

  rem *! Process all matching files in folder, seeing if any are month end
  for %%A in ("%~3") do (
    rem *!* Extract date components from filename
    set EomDay=
    set Filename=%%~nA
    call :DateParts -12 -8 -6 "%%~nA"
    REM call :DateParts -12 -8 -6 "DYNAMICS_db_201109302145"
    REM call :DateParts  -8 -4 -2 "Central-QBE-20140330"
    REM call :DateParts -10 -5 -2 "HappinessFarms v2014-03-28"

    rem *!* Determine what last day of month would be for this year / month
    call :EomDay "!YYYY!" "!MM!" "!DD!"

    rem *!* Is this file an end of month backup, if so save it if needed
    if "!DD!" EQU "!EomDay!" (
      if not exist "%~2\%%~A" copy "%%~A" "%~2"
    )
  )

  rem *!* Now remove any older files beyond the number of files we want to keep
  for /F "tokens=* skip=%~4" %%A in ('dir /b /a-d /o-d "%~3"') do del "%%~A"

  popd
  endlocal
  goto :EOF

rem *!* Extract the year, month and day from the file name based on the offsets specified
:DateParts [year-offset] [month-offset] [day-offset] [string]
  set String=%~4
  set YYYY=!String:~%1,4!
  set MM=!String:~%2,2!
  set DD=!String:~%3,2!
  echo [%~4], YYYY=[%YYYY%], MM=[%MM%], DD=[%DD%]
  exit /b

rem *!* Calculate last day of month for this year / month
:EomDay [YYYY] [MM] [DD]
  setlocal

  rem *!* Get number of days in this month
  for /F "tokens=%~2 delims=," %%A in ("31,28,31,30,31,30,31,31,30,31,30,31") do set Eom=%%A

  rem *!* If this is February, and a leap year, add one to last day of month
  if "%~2" EQU "02" (
    set /a Leap=%~1 %% 4
    if !Leap! EQU 0 set /A Eom+=1
  )

  rem *!* Return value to caller
  endlocal & set EomDay=%Eom%
  goto :EOF

Open in new window

~bp
0
 
Bill PrewCommented:
Do you need the script to intelligently handle all 3 formats at the same time, or will you be using a different version of the script for each format?

~bp
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
Yes it will be 3 different batch files, for 3 different servers/locations. I knew I had to count from the right but just couldn't get it to work. Will test it again and provide the results tonight=thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Lionel MMSmall Business IT ConsultantAuthor Commented:
Ok I got this to work
rem this is based on this filename format HappinessFarms v2014-02-01.QBW
    rem where 10 is the 10th letter from the right, etc.
    set YYYY=!Filename:~-10,4!
    set MM=!Filename:~-5,2!
    set DD=!Filename:~-2,2!
and changed the folder location, all the .bak to .qbw and it left 30 files and moved all the EOM files to the OEM folder. Will try the other one later today, als a .qbw one and let you know if it works--thanks.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
I used the same batch file that worked for Windows Server 2003 (the one noted above) and copied to a Windows 2008 server, changed the ProcessDir and the file dating numbers and now I get this error "9 delims=," was unexpected at this time." I am attaching the entire batch file for you to see.
EOM-CCI.txt
0
 
Bill PrewCommented:
Did you look at my post with a newer maybe cleaner approach to the date extraction?

As far as errors, I did notice in the script you posted that it had the following, which is missing a paren I believe, not sure exactly what you were trying to accomplish there, and I didn't see the called subroutine 'CheckEOM' defined either...

  rem *!* Now removeany older files beyond the number of files we want to keep
  rem	2013-05-28 for /F "tokens=* skip=%~3" %%A in ('dir /b /a-d /o-d "*.qbw"') do del "%%~A"
for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "%BaseDir%\%%~F\*.qbw" ') do (
        ECHO File:"%%~nA"
        call :CheckEOM "%%~nA"
  popd
  endlocal
  goto :EOF

Open in new window

~bp
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
Bill, tried the new format with call :DateParts -10 -5 -2 "HappinessFarms v2014-03-28". It now gives an "access is denied" error, however when I use the original script and use the set YYYY=!Filename:~-10,4!
    set MM=!Filename:~-5,2!
    set DD=!Filename:~-2,2!

It works--see two text files--thanks.
Works.txt
Fails.txt
0
 
Bill PrewCommented:
You need to pass the variable that has the name of the file in it, not a hard coded string.  I just did that to demonstrate the different offsets you could use for different format file names.

Change this:

    set Filename=%%~nA
    rem	call :DateParts -12 -8 -6 "%%~nA"
    REM call :DateParts -12 -8 -6 "DYNAMICS_db_201109302145"
    REM call :DateParts  -8 -4 -2 "Central-QBE-20140330"
    call :DateParts -10 -5 -2 "HappinessFarms v2014-03-28"

Open in new window

to:

    set Filename=%%~nA
    rem	call :DateParts -12 -8 -6 "%%~nA"
    REM call :DateParts -12 -8 -6 "DYNAMICS_db_201109302145"
    REM call :DateParts  -8 -4 -2 "Central-QBE-20140330"
    call :DateParts -10 -5 -2 "%%~nA"

Open in new window

~bp
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
Bill, using this I get an access denied error
rem *!* Extract date components from filename
    set EomDay=
    set Filename=%%~nA
    rem      call :DateParts -12 -8 -6 "%%~nA"
    call :DateParts -10 -5 -2 "%%~nA"
But using this--it works
rem *!* Extract date components from filename
    set EomDay=
    set Filename=%%~nA

    rem this is based on this filename format HappinessFarms v2014-02-01.QBW
    rem where 10 is the 10th letter from the right, etc.
    set YYYY=!Filename:~-10,4!
    set MM=!Filename:~-5,2!
    set DD=!Filename:~-2,2!

and I get this error on two different servers (obviously with different file/date extractions).
0
 
Bill PrewCommented:
Can you remove the @echo off at the top and run the script and then copy and post up the full output of the execution, along with the exact BAT script source code you ran please.

~bp
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
here is the batch file and the results of it is attched
rem	@echo off
setlocal EnableDelayedExpansion

rem *!* Process each folder
call :ProcessDir "C:\Data\Users\QuickBooks\Daily" "C:\Data\Users\QuickBooks\EOM" "*.qbw" 60

rem *!* Done, exit
goto :EOF

rem *!* Process a folder, saving month end files, and keeping only desired number of files
:ProcessDir [base-dir] [save-dir] [filter] [files-to=keep]
  setlocal
  pushd "%~1"

  rem *! Process all matching files in folder, seeing if any are month end
  for %%A in ("%~3") do (
    rem *!* Extract date components from filename
    set EomDay=
    set Filename=%%~nA
    rem	call :DateParts -12 -8 -6 "%%~nA"
    REM call :DateParts -12 -8 -6 "DYNAMICS_db_201109302145"
    call :DateParts  -8 -4 -2 "%%~nA"
    REM call :DateParts -10 -5 -2 "HappinessFarms v2014-03-28"

    rem *!* Determine what last day of month would be for this year / month
    call :EomDay "!YYYY!" "!MM!" "!DD!"

    rem *!* Is this file an end of month backup, if so save it if needed
    if "!DD!" EQU "!EomDay!" (
      if not exist "%~2\%%~A" copy "%%~A" "%~2"
    )
  )

  rem *!* Now remove any older files beyond the number of files we want to keep
  for /F "tokens=* skip=%~4" %%A in ('dir /b /a-d /o-d "%~3"') do del "%%~A"

  popd
  endlocal
  goto :EOF

rem *!* Extract the year, month and day from the file name based on the offsets specified
:DateParts [year-offset] [month-offset] [day-offset] [string]
  set String=%~4
  set YYYY=!String:~%1,4!
  set MM=!String:~%2,2!
  set DD=!String:~%3,2!
  echo [%~4], YYYY=[%YYYY%], MM=[%MM%], DD=[%DD%]
  exit /b

rem *!* Calculate last day of month for this year / month
:EomDay [YYYY] [MM] [DD]
  setlocal

  rem *!* Get number of days in this month
  for /F "tokens=%~2 delims=," %%A in ("31,28,31,30,31,30,31,31,30,31,30,31") do set Eom=%%A

  rem *!* If this is February, and a leap year, add one to last day of month
  if "%~2" EQU "02" (
    set /a Leap=%~1 %% 4
    if !Leap! EQU 0 set /A Eom+=1
  )

  rem *!* Return value to caller
  endlocal & set EomDay=%Eom%
  goto :EOF
                                            

Open in new window

EE-Results.txt
0
 
Bill PrewCommented:
Thanks.  I didn't see any access denied errors in that log.  And it looked like everything was working properly, the date fields were extracted correctly, and the EOM day was being calculated correctly.

I would need to see a log from a run that actually had the access denied error to dig deeper.

~bp
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
ran attached file as a .bat and got these results
[621 Rentals-20140328], YYYY=[2014], MM=[03], DD=[28]
[621 Rentals-20140329], YYYY=[2014], MM=[03], DD=[29]
[621 Rentals-20140330], YYYY=[2014], MM=[03], DD=[30]
[621 Rentals-20140401], YYYY=[2014], MM=[04], DD=[01]
[621 Rentals-20140402], YYYY=[2014], MM=[04], DD=[02]
[621 Rentals-20140403], YYYY=[2014], MM=[04], DD=[03]
[621 Rentals-20140405], YYYY=[2014], MM=[04], DD=[05]
[621 Rentals-20140406], YYYY=[2014], MM=[04], DD=[06]
[621 Rentals-20140407], YYYY=[2014], MM=[04], DD=[07]
[621 Rentals-20140408], YYYY=[2014], MM=[04], DD=[08]
[621 Rentals-20140409], YYYY=[2014], MM=[04], DD=[09]
[621 Rentals-20140410], YYYY=[2014], MM=[04], DD=[10]
[621 Rentals-20140411], YYYY=[2014], MM=[04], DD=[11]
[621 Rentals-20140412], YYYY=[2014], MM=[04], DD=[12]
[621 Rentals-20140413], YYYY=[2014], MM=[04], DD=[13]
[621 Rentals-20140415], YYYY=[2014], MM=[04], DD=[15]
[621 Rentals-20140416], YYYY=[2014], MM=[04], DD=[16]
[621 Rentals-20140417], YYYY=[2014], MM=[04], DD=[17]
[621 Rentals-20140418], YYYY=[2014], MM=[04], DD=[18]
[621 Rentals-20140419], YYYY=[2014], MM=[04], DD=[19]
[621 Rentals-20140420], YYYY=[2014], MM=[04], DD=[20]
[621 Rentals-20140421], YYYY=[2014], MM=[04], DD=[21]
[621 Rentals-20140422], YYYY=[2014], MM=[04], DD=[22]
[621 Rentals-20140423], YYYY=[2014], MM=[04], DD=[23]
[621 Rentals-20140424], YYYY=[2014], MM=[04], DD=[24]
[621 Rentals-20140425], YYYY=[2014], MM=[04], DD=[25]
[621 Rentals-20140426], YYYY=[2014], MM=[04], DD=[26]
[621 Rentals-20140427], YYYY=[2014], MM=[04], DD=[27]
[621 Rentals-20140428], YYYY=[2014], MM=[04], DD=[28]
[621 Rentals], YYYY=[ Ren], MM=[ta], DD=[ls]
ta delims=," was unexpected at this time.
[Central-QBE-20140326], YYYY=[2014], MM=[03], DD=[26]
[Central-QBE-20140327], YYYY=[2014], MM=[03], DD=[27]
[Central-QBE-20140328], YYYY=[2014], MM=[03], DD=[28]
[Central-QBE-20140329], YYYY=[2014], MM=[03], DD=[29]
[Central-QBE-20140330], YYYY=[2014], MM=[03], DD=[30]
[Central-QBE-20140401], YYYY=[2014], MM=[04], DD=[01]
[Central-QBE-20140402], YYYY=[2014], MM=[04], DD=[02]
[Central-QBE-20140403], YYYY=[2014], MM=[04], DD=[03]
[Central-QBE-20140405], YYYY=[2014], MM=[04], DD=[05]
[Central-QBE-20140406], YYYY=[2014], MM=[04], DD=[06]
[Central-QBE-20140407], YYYY=[2014], MM=[04], DD=[07]
[Central-QBE-20140408], YYYY=[2014], MM=[04], DD=[08]
[Central-QBE-20140409], YYYY=[2014], MM=[04], DD=[09]
[Central-QBE-20140410], YYYY=[2014], MM=[04], DD=[10]
[Central-QBE-20140411], YYYY=[2014], MM=[04], DD=[11]
[Central-QBE-20140412], YYYY=[2014], MM=[04], DD=[12]
[Central-QBE-20140413], YYYY=[2014], MM=[04], DD=[13]
[Central-QBE-20140415], YYYY=[2014], MM=[04], DD=[15]
[Central-QBE-20140416], YYYY=[2014], MM=[04], DD=[16]
[Central-QBE-20140417], YYYY=[2014], MM=[04], DD=[17]
[Central-QBE-20140418], YYYY=[2014], MM=[04], DD=[18]
[Central-QBE-20140419], YYYY=[2014], MM=[04], DD=[19]
[Central-QBE-20140420], YYYY=[2014], MM=[04], DD=[20]
[Central-QBE-20140421], YYYY=[2014], MM=[04], DD=[21]
[Central-QBE-20140422], YYYY=[2014], MM=[04], DD=[22]
[Central-QBE-20140423], YYYY=[2014], MM=[04], DD=[23]
[Central-QBE-20140424], YYYY=[2014], MM=[04], DD=[24]
[Central-QBE-20140425], YYYY=[2014], MM=[04], DD=[25]
[Central-QBE-20140426], YYYY=[2014], MM=[04], DD=[26]
[Central-QBE-20140427], YYYY=[2014], MM=[04], DD=[27]
[Central-QBE-20140428], YYYY=[2014], MM=[04], DD=[28]
C:\Data\Users\QuickBooks\Daily\621 Rentals.QBW
Access is denied.
EE-EOM-QB-CCI.txt
0
 
Bill PrewCommented:
It appears you had a file in the folder that you were processing that did not conform to the expected naming convention, and also was open by Quickbooks so could not be deleted.  The file was:

C:\Data\Users\QuickBooks\Daily\621 Rentals.QBW

~bp
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
OK I deleted that file and that fixed that one but on this one I still get the access denied error. I checekd folder and file security and user lmm (me) had Full Control of both folder and files in it.
[HappinessFarms v2014-03-13], YYYY=[2014], MM=[03], DD=[13]
[HappinessFarms v2014-03-14], YYYY=[2014], MM=[03], DD=[14]
[HappinessFarms v2014-03-15], YYYY=[2014], MM=[03], DD=[15]
[HappinessFarms v2014-03-16], YYYY=[2014], MM=[03], DD=[16]
[HappinessFarms v2014-03-17], YYYY=[2014], MM=[03], DD=[17]
[HappinessFarms v2014-03-18], YYYY=[2014], MM=[03], DD=[18]
[HappinessFarms v2014-03-19], YYYY=[2014], MM=[03], DD=[19]
[HappinessFarms v2014-03-20], YYYY=[2014], MM=[03], DD=[20]
[HappinessFarms v2014-03-21], YYYY=[2014], MM=[03], DD=[21]
[HappinessFarms v2014-03-22], YYYY=[2014], MM=[03], DD=[22]
[HappinessFarms v2014-03-23], YYYY=[2014], MM=[03], DD=[23]
[HappinessFarms v2014-03-24], YYYY=[2014], MM=[03], DD=[24]
[HappinessFarms v2014-03-25], YYYY=[2014], MM=[03], DD=[25]
[HappinessFarms v2014-03-26], YYYY=[2014], MM=[03], DD=[26]
[HappinessFarms v2014-03-27], YYYY=[2014], MM=[03], DD=[27]
[HappinessFarms v2014-03-28], YYYY=[2014], MM=[03], DD=[28]
[HappinessFarms v2014-03-29], YYYY=[2014], MM=[03], DD=[29]
[HappinessFarms v2014-03-30], YYYY=[2014], MM=[03], DD=[30]
[HappinessFarms v2014-03-31], YYYY=[2014], MM=[03], DD=[31]
[HappinessFarms v2014-04-01], YYYY=[2014], MM=[04], DD=[01]
[HappinessFarms v2014-04-02], YYYY=[2014], MM=[04], DD=[02]
[HappinessFarms v2014-04-03], YYYY=[2014], MM=[04], DD=[03]
[HappinessFarms v2014-04-04], YYYY=[2014], MM=[04], DD=[04]
[HappinessFarms v2014-04-05], YYYY=[2014], MM=[04], DD=[05]
[HappinessFarms v2014-04-06], YYYY=[2014], MM=[04], DD=[06]
[HappinessFarms v2014-04-07], YYYY=[2014], MM=[04], DD=[07]
[HappinessFarms v2014-04-08], YYYY=[2014], MM=[04], DD=[08]
[HappinessFarms v2014-04-09], YYYY=[2014], MM=[04], DD=[09]
[HappinessFarms v2014-04-10], YYYY=[2014], MM=[04], DD=[10]
[HappinessFarms v2014-04-11], YYYY=[2014], MM=[04], DD=[11]
[HappinessFarms v2014-04-12], YYYY=[2014], MM=[04], DD=[12]
[HappinessFarms v2014-04-13], YYYY=[2014], MM=[04], DD=[13]
[HappinessFarms v2014-04-14], YYYY=[2014], MM=[04], DD=[14]
[HappinessFarms v2014-04-15], YYYY=[2014], MM=[04], DD=[15]
[HappinessFarms v2014-04-16], YYYY=[2014], MM=[04], DD=[16]
[HappinessFarms v2014-04-17], YYYY=[2014], MM=[04], DD=[17]
[HappinessFarms v2014-04-18], YYYY=[2014], MM=[04], DD=[18]
[HappinessFarms v2014-04-19], YYYY=[2014], MM=[04], DD=[19]
[HappinessFarms v2014-04-20], YYYY=[2014], MM=[04], DD=[20]
[HappinessFarms v2014-04-21], YYYY=[2014], MM=[04], DD=[21]
[HappinessFarms v2014-04-22], YYYY=[2014], MM=[04], DD=[22]
[HappinessFarms v2014-04-23], YYYY=[2014], MM=[04], DD=[23]
[HappinessFarms v2014-04-24], YYYY=[2014], MM=[04], DD=[24]
[HappinessFarms v2014-04-25], YYYY=[2014], MM=[04], DD=[25]
[HappinessFarms v2014-04-26], YYYY=[2014], MM=[04], DD=[26]
[HappinessFarms v2014-04-27], YYYY=[2014], MM=[04], DD=[27]
[HappinessFarms v2014-04-28], YYYY=[2014], MM=[04], DD=[28]
[HappinessFarms v2014-04-29], YYYY=[2014], MM=[04], DD=[29]
C:\Backup\QBooks\HappinessFarms v2014-03-05.QBW
Access is denied.
C:\Backup\QBooks\HappinessFarms v2014-02-27.QBW
Access is denied.
C:\Backup\QBooks\HappinessFarms v2014-02-28.QBW
Access is denied.
C:\Backup\QBooks\HappinessFarms v2014-02-08.QBW
Access is denied.
C:\Backup\QBooks\HappinessFarms v2014-02-09.QBW
Access is denied.
C:\Backup\QBooks\HappinessFarms v2014-03-04.QBW
Access is denied.
C:\Backup\QBooks\HappinessFarms v2014-02-01.QBW
Access is denied.
C:\Backup\QBooks\HappinessFarms v2014-02-26.QBW
Access is denied.
C:\Backup\QBooks\HappinessFarms v2014-02-17.QBW
Access is denied.
EE-EOM-QB-HFI.txt
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
I got them all to work except the one from the last post so I concluded it is an anomaly and cleaned it up manually. If you recall in a very early solution we did not move files to an end of month folder--if I just wanted to delete excess files and not keep end of month how would do that again--trying to find the question I asked but for you this is probably an easy answer--if not I will ask a new question. Thanks for all you GREAT help.
0
 
Bill PrewCommented:
Do you mean you want to save the month end, but just leave it in it's folder (not move it someplace else), or do you mean you don't want to save it at all, and just keep the latest n files?

~bp
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
just keep the latest n files
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
BP I am missing one final piece in this solution--the batch file is not closing down the dos window when started--I added Exit to the end of the file but that did not work. How do I add an Exit so the dos window closes when done--thanks.
0
 
Bill PrewCommented:
How are you invoking the script, it should close the window when it is done automatically.

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.