Windows batch script to compare records older than 31 days & extract the records out

sunhux
sunhux used Ask the Experts™
on
I have a text file in 2 columns where column 1 is date in dd/mm/yyyy while column 2 is a user's name.

11/12/2018         aaron
28/12/2018         amber
5/1/2019              ben
2/11/2018            barney
2/1/2019              carl
. . .

Need a Windows batch script (not PowerShell) that when I run today (say it's 18 Jan 2019 today), would lik eto
extract out a list of users where the date in the file is more than 31 days from today's date.  So output will be:

11/12/2018         aaron
2/11/2018            barney
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PeeterBIT Support Tech

Commented:
Try this ..... save it as     test-date.vbs     then run at command prompt :      cscript test-date.vbs  
( your user file format syntax is not great ..... the first column date syntax is awkward and error prone ..... and assuming, in my script, that those are spaces between col1 and col2 ?? If they are tabs or something else then more code modification probably necessary ...)
 
filename = "userlist.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(filename)

date_now = Date
'on error resume next
Do Until f.AtEndOfStream
  line = f.ReadLine
  s1 = split(line, " ")
  d1 = split(cstr(s1(0)), "/")
  std_date_string = d1(2) + "/" + d1(1) + "/" + d1(0)
    
  date_diff = datediff("d", std_date_string, date_now)
  if date_diff > 31 Then
    wscript.echo line
  end if
  'wscript.echo(date_diff)
Loop

f.Close

Open in new window

IT / Software Engineering Consultant
Top Expert 2016
Commented:
Give this a try, seems to do the job here...

@echo off
setlocal EnableDelayedExpansion

REM Define input file, and days to keep old files
set FileIn=B:\EE\EE29132939\in.txt
set ReportDays=31

REM Make sure input file exists
if not exist "%FileIn%" (
    echo *ERROR* Input file "%FileIn%" not found.
    exit /b
)

REM Get current date in YYYYMMDD format, convert to julian
set LocalDateTime=
for /f "tokens=* skip=1" %%A in ('wmic os get LocalDateTime') do (
    if not defined LocalDateTime (
        set LocalDateTime=%%A
    )
)

REM Should never happen, but just be be extra safe
if not defined LocalDateTime (
    echo *ERROR* Unable to get current date.
    exit /b
)

REM Convert current date to julian, subtract days to report on for use below
call :jDate jToday %LocalDateTime:~0,8%
set /a jReportDate=jToday-ReportDays

REM Read input file and report on expired entries
for /f "tokens=1-4* delims=/ " %%A in ('type "%FileIn%"') do (
    REM Get parse values, left pad MM, DD with zero to two dogots
    set /a DD=100+%%A
    set /a MM=100+%%B
    set YYYY=%%C
    set Name=%%D
    set MM=!MM:~-2!
    set DD=!DD:~-2!

    REM Convert this records date to julian and see if it's old enough
    call :jDate jRecordDate !YYYY!!MM!!DD!
    if !jRecordDate! LSS !jReportDate! (
        echo !MM!/!DD!/!YYYY! !Name!
    )
)

REM Done
exit /b

REM Subroutine to calculate julian date from (YYYYMMDD) date
:jDate [return-variable] [date-string]
    set DateStr=%~2
    set yy=%DateStr:~0,4%
    set /A mm=1%DateStr:~4,2%-100
    set /A dd=1%DateStr:~6,2%-100
    set /a "yy=10000%yy% %%10000,mm=100%mm% %% 100,dd=100%dd% %% 100"
    set /a %~1=dd-32075+1461*(yy+4800+(mm-14)/12)/4+367*(mm-2-(mm-14)/12*12)/12-3*((yy+4900+(mm-14)/12)/100)/4
    exit /b

Open in new window


»bp

Author

Commented:
Thanks very much guys;  will play with it for 2 days & if no further clarifications, will close this thread.
PeeterBIT Support Tech
Commented:
@sunhux, Here's a slightly better version, that copes with spaces or other whitespace, between the columns in your  record layout .....
filename = "userlist.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(filename)

date_now = Date
'on error resume next
Do Until f.AtEndOfStream
  line = f.ReadLine
  Set re = New RegExp
  re.Pattern = "\s+"
  re.Global  = True

  line_temp = Trim(re.Replace(line, " "))
  s1 = split(line_temp, " ")
  d1 = split(cstr(s1(0)), "/")
  std_date_string = d1(2) + "/" + d1(1) + "/" + d1(0)
    
  date_diff = datediff("d", std_date_string, date_now)
  if date_diff > 31 Then
    wscript.echo line
  end if
  'wscript.echo(date_diff)
Loop

f.Close

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial