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

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
sunhuxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PeeterBIT Support TechCommented:
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

Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sunhuxAuthor Commented:
Thanks very much guys;  will play with it for 2 days & if no further clarifications, will close this thread.
PeeterBIT Support TechCommented:
@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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Batch

From novice to tech pro — start learning today.