1

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.

Mike Q
  • 6,716
  • 5
  • 55
  • 62
  • You should look into using [PowerShell](http://en.wikipedia.org/wiki/Windows_PowerShell). – mbeckish May 10 '13 at 13:49
  • If I use powershell script, I could call it from my batch script so that would be no problem I suppose. Do you have sample code from powershell? – Mike Q May 10 '13 at 13:51
  • No, sorry I don't. But Google has lots and lots of samples. – mbeckish May 10 '13 at 13:53

4 Answers4

2

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

Community
  • 1
  • 1
Endoro
  • 37,015
  • 8
  • 50
  • 63
  • I'm not sure about your date format, please provide this (are there leading zeros in month or day?) Please show the output of `echo %date%`. I adjusted date now for the following format: `Fr 05/10/2013`. Please let me know, if yours differs. – Endoro May 10 '13 at 15:06
  • 1
    Oh - very clever, sorting by using the variable name. Unfortunately, it doesn't work if there is more than one record for any date - you need to extend the name to include HH & MM and make some adjustment for AM/PM. Another improvement would be that all you need do as far as the date is concerned is work out (today - 100) and skip outputting any $... which has a name less than $... of the target date. – Magoo May 10 '13 at 15:33
  • @Peter - thank you for your comment! You are completely right, I have to add an additional property to the sort string to make it unique. I will ad a counter to avoid ugly AM/PM calculations and delayed expansion in the reading part of the code. To the second part of your comment: to realize this I need a reverse function of `JDN` **or** delayed expansion in the reading part, did I understand you right? – Endoro May 10 '13 at 16:08
  • 1
    @Endoro - yes, correct - but it's a minor matter. Perhaps it would be better like this: In the loop on `set $` : if defined TODAY (do the compare routine, compare to uday, if <100 diff, CLEAR TODAY) then IF NOT DEFINED TODAY outpuut the line. In that way, the long date-conversion is only executed until the first line-to-print is located - then TODAY is cleared, and that line and all others after are output. We don't need to check those others because your sneaky naming scheme means that only the ones which are after the target date will be left to process. – Magoo May 10 '13 at 16:55
  • OOh - you nearly had me agreeing with you on your add-a-line-number-to-distinguish-between-instances scheme. Not good enough, I'm afraid. The data is in random order, so same-date data won't necessarily be in time order...Painful, but relatively easy to overcome. ALSO, since the line number is original-line-number-from-source-data, line "10" will sort before lines "2..9" ... – Magoo May 10 '13 at 17:05
  • The counter has no impact on sorting (it is on the sorting line's end, [example](http://pastebin.com/4b4JwKDR)). – Endoro May 10 '13 at 17:43
  • Hey guys, I had a chance to look at the code today. I changed it around a bit and will post it below. I don't understand the local variable thing because I can't get it to change 1/1/2013 to 01/01/2013.... – Mike Q May 14 '13 at 23:47
1

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)

Magoo
  • 77,302
  • 8
  • 62
  • 84
  • Hey can you look at my code and tell me how to force the formatting to be 01/01/2001 opposed to 1/1/2001 and I'll vote yours up. (since it is beeing mainly used anyway... – Mike Q May 16 '13 at 19:32
0

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

CMS_95
  • 335
  • 3
  • 13
0

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
Magoo
  • 77,302
  • 8
  • 62
  • 84
Mike Q
  • 6,716
  • 5
  • 55
  • 62
  • I've added in the corrections required. Essentially, you weren't setting up RMONTH, RDAY and RHOUR, so these were being output as [nothing]. I've also `REM`med out your debug lines. The `datetojdn` routine requires YYYYMMDD format, so adding 100 to the month and day numbers, then using the last 2 characters should yield the required result (or, as I did, the string starting at position 1, which simply deletes the very first of the 3 characters) – Magoo May 17 '13 at 05:27
  • I spent a couple of hours trying to understand !var! and %%var and %var%... Thank you so so much for the help. I don't know that I would have done it without you. – Mike Q May 18 '13 at 00:27