Solved

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

Posted on 2014-04-01
20
1,018 Views
Last Modified: 2014-05-15
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
0
Comment
Question by:lionelmm
  • 11
  • 9
20 Comments
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39970202
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
 
LVL 51

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 39970331
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39970470
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39972125
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39972178
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39972906
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39973815
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39973895
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39976778
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39977068
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 24

Author Comment

by:lionelmm
ID: 39994560
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39995590
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
 
LVL 24

Author Comment

by:lionelmm
ID: 40030539
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 40030587
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
 
LVL 24

Author Comment

by:lionelmm
ID: 40034563
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
 
LVL 24

Author Closing Comment

by:lionelmm
ID: 40037018
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 40037315
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
 
LVL 24

Author Comment

by:lionelmm
ID: 40037491
just keep the latest n files
0
 
LVL 24

Author Comment

by:lionelmm
ID: 40044302
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 40066945
How are you invoking the script, it should close the window when it is done automatically.

~bp
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now