SORT Order not working correction

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
SIMMS7400
Posts: 442
Joined: 07 Jan 2016 07:47

SORT Order not working correction

#1 Post by SIMMS7400 » 09 Aug 2019 06:26

HI Folks -

For some reason, the SORT function is not working as I would expect. I am sorting the 5th column which has values from 1 through 12. However, when I do SORT /R it's saying the max is9, not 12.

Here is my code:

Code: Select all

	(  
		FOR %%F IN ("!DATAFILENAME!") DO (

		  REM Sort PERIOD Column in reverse order to get MAX value
			SET "MAX="
			SET "MIN="
			FOR /F "tokens=5 delims=|" %%A IN ('SORT /R "%%~F"') DO (
				IF NOT DEFINED MAX (
					SET "MAX=%%A"
				 )
			)
			
			REM Sort PERIOD Column in regular order to get MIN value
			FOR /F "tokens=5,6 delims=|" %%A IN ('SORT "%%~F"') DO (
				IF NOT DEFINED MIN (
					SET "MIN=%%A"
					SET "YEAR=%%B" & SET "YEAR=!YEAR:~2,2!"
				 )
			)
	   )
	)
POPD

echo !MIN! 
echo !MAX!
echo !YEAR!
pause
Here is data file
AC_41110|FH_00000|DP_000|CO_11|1|FY19|budget|-3344033.0
AC_41110|FH_00000|DP_000|CO_11|2|FY19|budget|-3464968.18
AC_41110|FH_00000|DP_000|CO_11|3|FY19|budget|-3734198.0
AC_41110|FH_00000|DP_000|CO_11|4|FY19|budget|-3743092.36
AC_41110|FH_00000|DP_000|CO_11|5|FY19|budget|-3492925.18
AC_41110|FH_00000|DP_000|CO_11|6|FY19|budget|-3517093.27
AC_41110|FH_00000|DP_000|CO_11|7|FY19|budget|-3762019.55
AC_41110|FH_00000|DP_000|CO_11|8|FY19|budget|-3770729.82
AC_41110|FH_00000|DP_000|CO_11|9|FY19|budget|-3477456.18
AC_41110|FH_00000|DP_000|CO_11|10|FY19|budget|-3942433.64
AC_41110|FH_00000|DP_000|CO_11|11|FY19|budget|-3289566.64
AC_41110|FH_00000|DP_000|CO_11|12|FY19|budget|-2968880.0
Do you know why? Thanks!

Aacini
Expert
Posts: 1605
Joined: 06 Dec 2011 22:15
Location: México City, México

Re: SORT Order not working correction

#2 Post by Aacini » 09 Aug 2019 07:08

Of course! SORT command sorts lines. In other words, sorts strings, and

Code: Select all

AC_41110|FH_00000|DP_000|CO_11|9|FY19|budget|-3477456.18
string is "larger" (that is, goes after) than

Code: Select all

AC_41110|FH_00000|DP_000|CO_11|12|FY19|budget|-2968880.0
string (because the "9" is larger than the "1").

Antonio

SIMMS7400
Posts: 442
Joined: 07 Jan 2016 07:47

Re: SORT Order not working correction

#3 Post by SIMMS7400 » 09 Aug 2019 09:42

HI Antonio -


Hmmm - I see and now understand. What would be my path forward to overcome this hurdle? I tried to strip out just that column but no luck. Can this still even be done?

Jer
Posts: 154
Joined: 23 Nov 2014 17:13
Location: California USA

Re: SORT Order not working correction

#4 Post by Jer » 09 Aug 2019 10:58

Here's my solution which is to create an artificial sort field. I'm looking forward to reading Antonio's solution.
I rearranged lines in the source text to show that it works.

Code: Select all

@echo off
setlocal EnableDelayedExpansion
set "srce=source.txt"
set "del=|"
>_temp.txt (
  For /F "tokens=1-6* delims=%del%" %%a In (%srce%) Do (
    set "dy=%%e" & set "dy=0!dy!"
    set "dy=!dy:~-2!"
    echo !dy!~%%a^%del%%%b^%del%%%c^%del%%%d^%del%%%e^%del%%%f^%del%%%g
  )
)
sort _temp.txt>tmpsort.txt
>newsource.txt (For /F "tokens=2* delims=~" %%a In (tmpsort.txt) Do echo %%a)
echo source.txt contents: & type source.txt &echo(
echo newsource.txt contents:
type newsource.txt & DEL tmpsort.txt & DEL _temp.txt
endlocal & exit /b
source.txt contents:
AC_41110|FH_00000|DP_000|CO_11|12|FY19|budget|-2968880.0
AC_41110|FH_00000|DP_000|CO_11|1|FY19|budget|-3344033.0
AC_41110|FH_00000|DP_000|CO_11|2|FY19|budget|-3464968.18
AC_41110|FH_00000|DP_000|CO_11|3|FY19|budget|-3734198.0
AC_41110|FH_00000|DP_000|CO_11|4|FY19|budget|-3743092.36
AC_41110|FH_00000|DP_000|CO_11|5|FY19|budget|-3492925.18
AC_41110|FH_00000|DP_000|CO_11|10|FY19|budget|-3942433.64
AC_41110|FH_00000|DP_000|CO_11|6|FY19|budget|-3517093.27
AC_41110|FH_00000|DP_000|CO_11|7|FY19|budget|-3762019.55
AC_41110|FH_00000|DP_000|CO_11|8|FY19|budget|-3770729.82
AC_41110|FH_00000|DP_000|CO_11|11|FY19|budget|-3289566.64
AC_41110|FH_00000|DP_000|CO_11|9|FY19|budget|-3477456.18

newsource.txt contents:
AC_41110|FH_00000|DP_000|CO_11|1|FY19|budget|-3344033.0
AC_41110|FH_00000|DP_000|CO_11|2|FY19|budget|-3464968.18
AC_41110|FH_00000|DP_000|CO_11|3|FY19|budget|-3734198.0
AC_41110|FH_00000|DP_000|CO_11|4|FY19|budget|-3743092.36
AC_41110|FH_00000|DP_000|CO_11|5|FY19|budget|-3492925.18
AC_41110|FH_00000|DP_000|CO_11|6|FY19|budget|-3517093.27
AC_41110|FH_00000|DP_000|CO_11|7|FY19|budget|-3762019.55
AC_41110|FH_00000|DP_000|CO_11|8|FY19|budget|-3770729.82
AC_41110|FH_00000|DP_000|CO_11|9|FY19|budget|-3477456.18
AC_41110|FH_00000|DP_000|CO_11|10|FY19|budget|-3942433.64
AC_41110|FH_00000|DP_000|CO_11|11|FY19|budget|-3289566.64
AC_41110|FH_00000|DP_000|CO_11|12|FY19|budget|-2968880.0

SIMMS7400
Posts: 442
Joined: 07 Jan 2016 07:47

Re: SORT Order not working correction

#5 Post by SIMMS7400 » 09 Aug 2019 18:22

Thank you! Let me play around with this and see if I can get it to work. Ultimately, I need MIN and MAX, I can adjust and see what it renders.

Thanks!

Aacini
Expert
Posts: 1605
Joined: 06 Dec 2011 22:15
Location: México City, México

Re: SORT Order not working correction

#6 Post by Aacini » 09 Aug 2019 20:37

Are you using SORT not for sorting, but to get maximum and minimum? And two times? :shock: Strange thing...

Why not keep things simple? Try:

Code: Select all

@echo off
setlocal

set /A max=-99999, min=99999
for /F "usebackq tokens=5,6 delims=|" %%a in ("%%~F") do (
   if %%a gtr !max! set "max=%%a"
   if %%a lss !min! set "min=%%a" & set "year=%%b"
)


echo !min!
echo !max!
echo !year:~2,2!
Antonio

aGerman
Expert
Posts: 3643
Joined: 22 Jan 2010 18:01
Location: Germany

Re: SORT Order not working correction

#7 Post by aGerman » 11 Aug 2019 06:16

I absolutely agree with Antonio in this case.
Once you really need a logical sorting you can use this hybrid macro:
Sort logically like in Windows Explorer (Powershell hybrid)

Steffen

Eureka!
Posts: 14
Joined: 25 Jul 2019 18:25

Re: SORT Order not working correction

#8 Post by Eureka! » 11 Aug 2019 08:50

And yet another way:

If you have cygwin or Linux Subsystem for Windows (*) installed, you can do:

Code: Select all

c:\Tools\cygwin64\bin\sort --field-separator  "|" +4 --numeric-sort filename.txt
or short: sort -t "|" +4 -n sort filename.txt
(sort by field 4 (first=0) ; use natural sort)


(*) or whatever it is called this month ..

SIMMS7400
Posts: 442
Joined: 07 Jan 2016 07:47

Re: SORT Order not working correction

#9 Post by SIMMS7400 » 12 Aug 2019 17:53

Aacini wrote:
09 Aug 2019 20:37
Are you using SORT not for sorting, but to get maximum and minimum? And two times? :shock: Strange thing...

Why not keep things simple? Try:

Code: Select all

@echo off
setlocal

set /A max=-99999, min=99999
for /F "usebackq tokens=5,6 delims=|" %%a in ("%%~F") do (
   if %%a gtr !max! set "max=%%a"
   if %%a lss !min! set "min=%%a" & set "year=%%b"
)


echo !min!
echo !max!
echo !year:~2,2!
Antonio
Antonio - that worked perfectly! I was making it much harder than it needed to be.

Thanks again for your help as always!!!

Post Reply