Page 1 of 1
SORT Order not working correction
Posted: 09 Aug 2019 06:26
by SIMMS7400
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!
Re: SORT Order not working correction
Posted: 09 Aug 2019 07:08
by Aacini
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
Re: SORT Order not working correction
Posted: 09 Aug 2019 09:42
by SIMMS7400
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?
Re: SORT Order not working correction
Posted: 09 Aug 2019 10:58
by Jer
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
Re: SORT Order not working correction
Posted: 09 Aug 2019 18:22
by SIMMS7400
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!
Re: SORT Order not working correction
Posted: 09 Aug 2019 20:37
by Aacini
Are you using SORT not for sorting, but to get maximum and minimum? And
two times?
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
Re: SORT Order not working correction
Posted: 11 Aug 2019 06:16
by aGerman
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
Re: SORT Order not working correction
Posted: 11 Aug 2019 08:50
by Eureka!
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 ..
Re: SORT Order not working correction
Posted: 12 Aug 2019 17:53
by SIMMS7400
Aacini wrote: ↑09 Aug 2019 20:37
Are you using SORT not for sorting, but to get maximum and minimum? And
two times?
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!!!