CODEWITHSUNDEEP
batch-file
Mike Q
Mike Q

Reputation: 7327

Sorting a text file by date with windows batch script

I have in interesting issue, I need to sort a list of items BY DATE in a text file which look like this:

    http://www.boatus.com/sailing,1/21/2013 9:00 PM
    http://www.boatus.com/powerboat,3/21/2012 10:00 PM
    http://www.boatus.com/games.html,5/20/2013 10:00 PM
    http://www.boatus.com/,4/11/2013 10:00 PM
    http://www.boatus.com/pressroom/prrss.asp,4/21/2013 10:00 PM
    http://www.boatus.com/,4/20/2013 9:00 PM

I also only need say 100 days of history. So if today is 5/10/2013, I only want from 1/30/2013 - 5/10/2013. Hence the list above should then look like this after sorting :

    http://www.boatus.com/,4/11/2013 10:00 PM
    http://www.boatus.com/,4/20/2013 9:00 PM
    http://www.boatus.com/pressroom/prrss.asp,4/21/2013 10:00 PM
    http://www.boatus.com/games.html,5/20/2013 10:00 PM

Hence only the past 100 days are listed in order by date.

Upvotes: 2

Views: 3150

Answers (4)

Mike Q
Mike Q

Reputation: 7327

My code at the moment, thanks guys for your help so far !!!! I still need it to change date format to YYYYMMDD, something wrong with the code.. grrr

    @ECHO OFF
    SETLOCAL enabledelayedexpansion

    for /f "tokens=1-3delims=/" %%i in ("%date:~4,10%") do set /a month=%%i, day=%%j, year=%%k
    if %month% lss 10 set "month=0%month%"
    if %day% lss 10 set "day=0%day%"
    call:DateToJDN %year%%month%%day% today
    echo Today %today% %year%%month%%day% 

    (
    FOR /f "tokens=1*delims=, " %%h IN (file.txt) DO (
     FOR /f "tokens=1-7delims=/: " %%a IN ("%%i") DO (

      SET /A RMONTH=100+%%a, RDAY=100+%%b
      SET /a RHOUR=%%d+100
      IF %%d==12 SET RHOUR=100
      call:DateToJDN %%c!RMONTH:~1!!RDAY:~1! rtoday
      REM call:DateToJDN %%c%%a%%b rtoday
      set /a diffdays=!today!-!rtoday!
      REM echo diffdays ===  !today!-!rtoday! = !diffdays!
      if !diffdays! leq 100 (
        ECHO %%c!rmonth!!rday!%%f!rhour!%%e,%%h,%%i
      )
     )
    )
    )>sorttemp.txt
    (
    FOR /f "tokens=1*delims=," %%i IN ('sort ^<sorttemp.txt') DO ECHO %%j
    )>output.txt
    TYPE output.txt
    ::: DEL sorttemp.txt /F /Q
    notepad sorttemp.txt
    GOTO :eof

    :DateToJDN yyyymmdd jdn=
    setlocal
    set date=%1
    REM echo %date%
    set /A yy=%date:~0,4%, mm=1%date:~4,2% %% 100, dd=1%date:~-2% %% 100
    set /A a=mm-14, jdn=(1461*(yy+4800+a/12))/4+(367*(mm-2-12*(a/12)))/12-(3*((yy+4900+a/12)/100))/4+dd-32075
    endlocal & set %2=%jdn%
    exit /B

    endlocal

Upvotes: 0

Endoro
Endoro

Reputation: 37569

Try this (list is in file.txt):

@echo off &setlocal
for /f "tokens=1*delims=[] " %%x in ('^<file.txt find /n /v ""') do (
    for /f "tokens=2delims=, " %%j in ("%%y") do (
        for /f "tokens=1-3delims=/" %%a in ("%%j") do (
            if %%a lss 10 if %%b lss 10 set "$%%c0%%a0%%b%%x=%%y"
            if %%a lss 10 if %%b geq 10 set "$%%c0%%a%%b%%x=%%y"
            if %%a geq 10 if %%b lss 10 set "$%%c%%a0%%b%%x=%%y"
        )
    )   
)
for /f "tokens=1-4delims=/ " %%i in ("%date%") do set /a month=1%%j-100, day=1%%k-100, year=%%l
if %month% lss 10 set "month=0%month%"
if %day% lss 10 set "day=0%day%"
call:DateToJDN %year%%month%%day% today
setlocal enabledelayedexpansion
for /f "tokens=1*delims=$=" %%i in ('set "$"') do (
    if defined today (
        call:DateToJDN %%i uday
        set /a diffdays=today-uday
        if !diffdays! leq 100 set "today="&echo(%%j
    ) else echo(%%j
)
endlocal
goto:eof

:DateToJDN yyyymmdd jdn=
setlocal
set date=%1
set /A yy=%date:~0,4%, mm=1%date:~4,2% %% 100, dd=1%date:~6,2% %% 100
set /A a=mm-14, jdn=(1461*(yy+4800+a/12))/4+(367*(mm-2-12*(a/12)))/12-(3*((yy+4900+a/12)/100))/4+dd-32075
endlocal & set %2=%jdn%
exit /B

Edit: added code improvements concerning Peter Wright's comment

JDN

Upvotes: 2

CMS_95
CMS_95

Reputation: 335

if the data you are sorting is already in text form I think you could copy and paste to a new excel doc and sort by date

Upvotes: 0

Magoo
Magoo

Reputation: 79983

Here's my version since I've written it - I didn't bother about the 'last 100 days' part which makes it easy if you change your mind.

@ECHO OFF
SETLOCAL enabledelayedexpansion
(
FOR /f "tokens=1*delims=, " %%h IN (swydf.txt) DO (
 FOR /f "tokens=1-7delims=/: " %%a IN ("%%i") DO (
  SET /a rmonth=10+%%a
  SET /a rday=10+%%b
  SET /a rhour=10+%%d
  IF %%d==12 (SET rhour=10)
  ECHO %%c!rmonth!!rday!%%f!rhour!%%e,%%h,%%i
 )
)
)>sorttemp.txt
(
FOR /f "tokens=1*delims=," %%i IN ('sort ^<sorttemp.txt') DO ECHO %%j
)>output.txt
TYPE output.txt
DEL sorttemp.txt /F /Q
GOTO :eof

Essentially, the date and time elements are converted to a constant-length field each by adding 10 (since all bar minutes are 1..31 - making 11..41) then setting hour to 10 if it's 12 (thus 12AM->10 sorts before 1AM->11)

Sort the result of YEAR+MONTH+DAY+AMPM+HOUR+MIN+,+HTTP...+,+data and output the remainder after the first token (delimited by comma)

Upvotes: 1

Related Questions