Need help to sort list date/time

[brinda]

hi all

I would like help on how we can sort date and time in a file list

tried to search the forum using sort date time list but could not come to anything near. It's probable i am not searching correctly

currently using rpsort to sort in sequence
time,
AM/PM,
year,
month
date.

Problem is after 12pm or AM the sort does not work



The format of the list is as below it is derived from dir and echo ~t etc. This is done so the entire code could be viewed in browser. All the filenames are unique. None overlaps.

Need help and guidance on how the same dir /od (sort with oldest first and newest last could be achieved in a list using dos batch.

thanks.

I have cut the end part so the list looks readable. The date time stamp is always in the front as shown below

Code: Select all

04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 12:35 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 10:30 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 02:47 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 03:22 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 04:31 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 04:58 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 05:26 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 05:53 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 06:52 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 07:17 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 07:42 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:41 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 09:10 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 03:09 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 08:01 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 03:58 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 06:27 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 11:53 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 11:56 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 02:53 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 05:56 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 06:08 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:37 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:47 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:51 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 12:25 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 03:51 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 04:13 AM  <a href="D:\Documents\Downloads\trace

[Endoro]

Try this:

Code: Select all

@ECHO OFF & setlocal
set /a count=1000000
for /f "delims=" %%i in (FILELIST.TXT) do (
   set /a count+=1
   set "line0=%%i"
   setlocal enabledelayedexpansion
   set "line=!line0:~6,4!!line0:~0,2!!line0:~3,2!!line0:~17,1!!line0:~11,2!!line0:~14,2!!count!"
   set "$!line!=!line0!"
   for /f "delims=" %%i in ('set "$"') do (if "!"=="" endlocal)&set "%%i"
)  
for /f "tokens=1*delims==" %%i in ('set "$"') do echo(%%j

But I'm not really sure, what you mean ...

[brinda]

endoro,


sorry probably my wordings are not correct.

1) i would like to sort the list with the oldest date/time stamp in the first line and onwards.

example

Code: Select all

04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 12:35 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 10:30 PM  <a href="D:\Documents\Downloads\trace

after sort would be

Code: Select all

04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 10:30 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 12:35 AM  <a href="D:\Documents\Downloads\trace

ran your code which gave me the following. Is there a way to get the original line after sorted as in example above.

thanks.

corrected %%j

Code: Select all

for /f "tokens=1*delims==" %%j in ('set "$"') do echo %%j >> m.txt

Code: Select all

$20130416A06521000010 
$20130416A07171000011 
$20130416A08411000014 
$20130416A09101000015 
$20130416P02471000004 
$20130416P03221000005 
$20130416P03511000030 
$20130416P03581000018 
$20130416P04311000006 
$20130416P04581000007 
$20130416P05261000008 
$20130416P05531000009 
$20130416P06271000019 
$20130416P07421000012 
$20130416P08211000001 
$20130416P08211000013 
$20130416P08211000028 
$20130416P10301000003 
$20130416P11531000020 
$20130416P11561000021 
$20130416P12371000025 
$20130416P12471000026 
$20130416P12511000027 
$20130417A03091000016 
$20130417A04131000031 
$20130417A06081000024 
$20130417A08011000017 
$20130417A12251000029 
$20130417A12351000002 
$20130417P02531000022 
$20130417P05561000023 

[Aacini]

You just need to add 12 to the hour if followed by PM, and reorder the date/time data as YYYYMMDDHHMM. You may insert this DateTime string in the output lines if you want to order the data with a posterior SORT command, or you may sort the data using memory variables and output the same data already sorted. The Batch file below use the last method.

EDIT: I corrected the program in accordance with Liviu's observation: "if hour < 12 and PM then hour += 12 else if hour == 12 and AM then hour = 0".

Code: Select all

@echo off
setlocal EnableDelayedExpansion

rem Separate the names in the list in date (%%a-%c) and time (%%d-%%f) parts, and the rest of the line in %%g
for /F "tokens=1-6* delims=/: " %%a in (originalFileList.txt) do (
   set /A hour=1%%d %% 100
   if "%%f" equ "PM" (
      if !hour! lss 12 set /A hour+=12
   ) else (
      if !hour! equ 12 set hour=0
   )
   if !hour! lss 10 set hour=0!hour!
   rem Insert the data in an array that is kept sorted in YYYYMMDDHHMM order
   set "data[%%c%%b%%a!hour!%%e]=%%a/%%b/%%c %%d:%%e %%f %%g"
)

rem Output the data in the array (sorted) order
(for /F "tokens=1* delims==" %%a in ('set data[') do (
   set "line=%%b"
   echo !line!
)) > sortedFileList.txt

This Batch file delete empty lines and exclamation marks in the input file. These limitations may be fixed, if needed.

Antonio

[Endoro]

corrected %%j

Why do you correct this? The output from my code is:

Code: Select all

04/16/2013 06:52 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 07:17 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:41 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 09:10 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 02:47 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 03:22 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 03:51 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 03:58 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 04:31 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 04:58 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 05:26 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 05:53 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 06:27 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 07:42 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 10:30 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 11:53 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 11:56 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:37 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:47 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:51 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 03:09 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 04:13 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 06:08 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 08:01 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 12:25 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 12:35 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 02:53 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 05:56 PM  <a href="D:\Documents\Downloads\trace

Btw. my code can deal with ugly signs:

Code: Select all

04/17/2013 04:13 AM  <a href="D:\Documents\Downloads\trace & ^ ! < > | %

[Liviu]

The output from my code is:

Code: Select all

04/16/2013 11:56 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:37 PM  <a href="D:\Documents\Downloads\trace
...
04/17/2013 08:01 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 12:25 AM  <a href="D:\Documents\Downloads\trace

What I think brinda meant is that the two pairs above are not sorted correctly. Note that 12pm = noon = 12:00, and 12am = midnight = 00:00 = 24:00.

Aacini's idea of normalizing the time to a 24-hour clock is right, but his rule "add 12 to the hour if followed by PM" also doesn't account for 12am/pm, and would need to be rephrased as "if hour < 12 and PM then hour += 12 else if hour == 12 and AM then hour = 0".

Liviu

[Endoro]

@Liviu: thank you!

Code: Select all

@ECHO OFF & setlocal
set /a count=1000000
for /f "delims=" %%i in (FILELIST.TXT) do (
   set /a count+=1
   set "line0=%%i"
   setlocal enabledelayedexpansion
   set /a hour=1!line0:~11,2!-100
   if !hour! lss 12 if "!line0:~17,1!" equ "P" set /a hour+=12
   if "!line0:~17,1!" equ "A" if !hour! equ 12 set /a hour=0
   if !hour! lss 10 set hour=0!hour!
   set "line=!line0:~6,4!!line0:~0,2!!line0:~3,2!!hour!line0:~14,2!!count!"
   set "$!line!=!line0!"
   for /f "delims=" %%i in ('set "$"') do (if "!"=="" endlocal)&set "%%i"
)  
for /f "tokens=1*delims==" %%i in ('set "$"') do echo(%%j

Output is:

Code: Select all

04/16/2013 06:52 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 07:17 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:41 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 09:10 AM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:37 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:47 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 12:51 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 02:47 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 03:22 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 03:58 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 03:51 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 04:31 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 04:58 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 05:26 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 05:53 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 06:27 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 07:42 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 08:21 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 10:30 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 11:53 PM  <a href="D:\Documents\Downloads\trace
04/16/2013 11:56 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 12:35 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 12:25 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 03:09 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 04:13 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 06:08 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 08:01 AM  <a href="D:\Documents\Downloads\trace
04/17/2013 02:53 PM  <a href="D:\Documents\Downloads\trace
04/17/2013 05:56 PM  <a href="D:\Documents\Downloads\trace

[Aacini]

Aacini's idea of normalizing the time to a 24-hour clock is right, but his rule "add 12 to the hour if followed by PM" also doesn't account for 12am/pm, and would need to be rephrased as "if hour < 12 and PM then hour += 12 else if hour == 12 and AM then hour = 0".

Liviu

Yes, you are right! I just fixed my code...

Antonio

[Endoro]

OMG, forgot an exclam!

Code: Select all

@ECHO OFF & setlocal
set /a count=1000000
for /f "delims=" %%i in (FILELIST.TXT) do (
   set /a count+=1
   set "line0=%%i"
   setlocal enabledelayedexpansion
   set /a hour=1!line0:~11,2!-100
   if !hour! lss 12 if "!line0:~17,1!" equ "P" set /a hour+=12
   if "!line0:~17,1!" equ "A" if !hour! equ 12 set /a hour=0
   if !hour! lss 10 set hour=0!hour!
   set "line=!line0:~6,4!!line0:~0,2!!line0:~3,2!!hour!!line0:~14,2!!count!"
   set "$!line!=!line0!"
   for /f "delims=" %%i in ('set "$"') do (if "!"=="" endlocal)&set "%%i"
)  
for /f "tokens=1*delims==" %%i in ('set "$"') do echo(%%j

[dbenham]

The task is very easily and efficiently accomplished using my REPL.BAT utility, found at regex search and replace for batch - Easily edit files!

All that is needed is one fairly long line with 4 pipes:

1) REPL - extract and prefix each line with a reformatted timestamp: yyyy/mm/dd ?Mhh:mm
2) REPL - substitute 00 for hour of 12
3) SORT the intermediate result
4) REPL - remove the reformatted timestamp prefix, leaving the original lines in sorted order

This will sort the file to the screen

Code: Select all

type filelist.txt|repl "^(../..)/(....) (..:.. )(..).*" "$2/$1 $4$3 $&"|repl "(^.{13})12" "$100"|sort|repl "^.{20}" ""

If you want to modify the file itself to be in sorted order

Code: Select all

type filelist.txt|repl "^(../..)/(....) (..:.. )(..).*" "$2/$1 $4$3 $&"|repl "(^.{13})12" "$100"|sort|repl "^.{20}" "">filelist.txt.new
move /y filelist.txt.new filelist.txt >nul

Dave Benham

[brinda]

Liviu,
thanks . sorry my english is not good in explaining problems in detail. Your explanation helps a lot.

Endoro, Aacini,
Thanks so much for the superb code. it works like a charm.

dbenham,
your code is something. I thought at first it is a search and replace. Did not know a hybrid is built in with only what the mind can think of.

Thanks all for the quick answer.

brinda