Page 1 of 1
conditional formatting and sorting
Posted: 31 May 2011 02:52
by Amin
Hi All
Can you please help me do the following:
file1:
==========
num1 num2 num3 word1
numx numy numz word2
31 0 20 hkl
31 0 2 xyz
30 2 24 hjk
.
.
file2:
==========
Num0 num1 num2 num3 word
Numy numx numn num7 wordx
1022 31 1 0 YES
1021 31 1 25 YES
1020 31 0 0 YES
1019 31 0 25 YES
1018 31 0 1 NO
1017 31 0 24 NO
1016 31 0 2 NO
1002 31 0 9 NO
1001 31 0 20 NO
1000 31 0 21 NO
999 31 0 11 NO
998 30 2 0 YES
997 30 2 25 YES
996 30 2 1 NO
995 30 2 24 NO
994 30 2 2 NO
993 30 2 13 NO
984 30 2 7 NO
983 30 2 18 NO
the needed:
===========
1-first requirement:
-------------------
output file-1 with preceeding column that is taken from file2 (column1) according to the other columns condition
(if 1st 2nd and 3rd columns of file1 equals 2nd 3rd and fourth in file2 add corresponding column1 from file2)
column1file2x num1 num2 num3 word1
column1file2y numx numy numz word2
1001 31 0 20 hkl
1016 31 0 2 xyz
995 30 2 24 hjk
2-second requirement:
----------------------
sorting of the output (requirement1) based on the 4th column which include numbers between 0 & 25 for each column1 and column2 values.
like this
31 0 (sort from 0-25)
31 1 (sort from 0 -25)
30 2 (sort from 0-25)
like this
num1 31 0 0 word0
num2 31 0 1 word1
num3 31 0 2 word2
num4 31 0 3 word3
. . . . .
numn 31 0 25 word25
num 30 2 0 W
num 30 2 1 Wrd
num 30 2 2 wrd2
num 30 2 3 anywrd
. . .
. . .
numx 30 2 25 wd
.
.
.
condition for rquirement2:
--------------------------
sometimes there will be missing number of column-4 (0 -25) ,i need if it is missing to print column2,3,4 values and the 5th column(the word) value to "Empty".
ex:
if column 4 (0-25) is missing the number 12 for (column1=31 column2=0),then print empty in the fifth column
ex:
31 0 12 EMPTY
Thank you in advance!
Re: conditional formatting and sorting
Posted: 31 May 2011 09:24
by dbenham
This looks like an ugly problem for batch - best done in some other scripting language like perl.
But it is doable - I suspect the simplest solution may be slow:
- Issue FINDSTR against file 2 for every row in file 1.
- build a temp result file in a format that supports sorting: numbers with leading zeros or in hex format via num2hex, sorted columns in the beginning because columns not fixed width.
- reformat the sorted output into desired format.
Q1) What if file 1 row doesn't appear in file 2? Is this where your last requirement dealing with EMPTY comes from?
Q2) What if file 1 row appears in file 2 multiple times with different value in column 1?
Q3) Output should contain column 4 from file 1 and column 5 from file 2 is ignored? Unless EMPTY condition?
If you provide small samples for file 1 and file 2 along with expected output (hand constructed) for those inputs, I might take this on. Either way it will help anyone that attempts to solve this.
Dave Benham
Re: conditional formatting and sorting
Posted: 31 May 2011 09:42
by Ed Dyreen
@dbenham
Do you work for amnesty international
Or can you solve it in like under 2 minutes ?
you are good
Re: conditional formatting and sorting
Posted: 01 Jun 2011 01:23
by Amin
HI dbenham
*Firstly: i want to answer your questions:
Q1: there is a condition i mentioned, only if columns are equal add column1 from file2, this is not related to Empty case.
Q2:This will not happen.
Q3: column 5 from file2 is not necessary at all.
*I will clarify it more ,
consider if i am doing a survey for a server room , and it contains cabinets,each cabinet contains magazines and each magazine contains 25 board slots.
consider the following in file1 heading:
cab MAG SL PROD
where
cab=cabinet number
MAG=magazine number
SL=slot number
PROD=the product name for the board or server.
Regarding the EMPTY case: i mean if the slot has no server or board it will not appear in the printout ,so i need to mention that this slot is empty, so for the empty case,we don't depend on file1 or file2 ,we assume that there should be slot1-25 , and if not,we will add this slot as empty.
///////////////////////////example files/////////////////////////
file1:
========
cab MAG SL PROD
50 4 14 product-1
50 4 13 product-1
50 5 10 product2-x
50 5 5 product2-x
50 5 22 product2-x
50 5 21 product2-x
50 5 19 product2-x
50 5 18 product2-x
50 5 16 product2-x
50 5 8 product2-x
50 5 3 product2-x
50 5 2 product2-x
40 1 18 addd
40 1 12 addd
40 1 8 JJJB-8
50 5 13 xyz
50 5 17 xyz
50 5 7 product2-TRH
50 5 12 xyz
40 0 25 zzz-w
50 76 0 FFFF
50 82 0 FFFF
50 88 0 FFFF
40 32 0 FFFF
40 40 0 FFFF
40 16 0 FFFF
40 1 4 UUUU
30 2 9 ttttt-1-77
30 2 10 prdct
40 1 10 DDD-SASF
40 1 25 zzz-w
40 1 7 kkB
50 3 6 llllB
30 2 11 mmm-2
40 24 0 FFFF
30 48 0 FFFF
50 64 0 FFFF
50 70 0 FFFF
40 1 20 DDD-SASF
40 1 14 DDD-DVD
40 1 5 JJJB-8
40 1 0 zzz-w
40 0 11 mmm-2
40 0 10 prdct
40 0 9 ttttt-1-77
40 0 5 product-1
40 0 6 product2-TRH
40 0 7 product2-TRH
40 0 8 ttttt-1-77
40 0 4 product-1
40 0 3 BBBB
40 0 2 BBBB
40 0 1 asdf-2
40 0 24 asdf-2
40 0 23 ZBM
40 0 22 ZBM
40 0 21 product2-TRH
40 0 20 ttttt-1-77
40 0 19 ttttt-1-77
40 0 18 product2-TRH
40 0 17 product2-TRH
40 0 16 product2-TRH
40 0 15 product2-TRH
40 0 14 BBBB
40 0 13 BBBB
40 0 0 zzz-w
30 2 25 zzz-w
50 5 11 product2-x
50 5 14 xyz
50 5 15 xyz
30 2 8 ttttt-1-77
30 2 7 xyz
30 2 6 xyz
30 2 5 xyz
30 2 4 xyz
30 2 3 product2-TRH
30 2 2 product2-TRH
30 2 1 asdf-2
30 2 24 asdf-2
30 2 23 ZBM
30 2 22 ZBM
30 2 21 xyz
30 2 20 ttttt-1-77
30 2 19 ttttt-1-77
30 2 18 xyz
30 2 17 xyz
30 2 16 xyz
30 2 15 xyz
30 2 14 xyz
30 2 13 xyz
30 2 0 zzz-w
50 3 25 zzz-w
50 3 12 product2-x
50 3 11 xyz
50 3 10 xyz
50 3 9 ttttt-1-77
50 3 8 ttttt-1-77
50 3 7 product-2
50 3 5 xyz
50 3 4 xyz
50 3 3 xyz
50 3 2 xyz
50 3 1 asdf-2
50 3 21 xyz
50 3 22 xyz
50 3 23 product-2
50 5 24 asdf-2
50 5 4 ttttt-1-77
50 3 19 ttttt-1-77
50 3 18 xyz
50 3 17 xyz
50 3 15 product2-x
50 3 14 product2-x
50 3 13 product2-x
50 3 0 zzz-w
50 4 25 zzz-w
50 4 11 product-2
50 4 10 product-2
50 4 9 ttttt-1-77
50 4 8 ttttt-1-77
50 4 3 product-2
50 4 2 product-2
50 4 1 asdf-2
50 4 24 asdf-2
50 4 22 product-2
50 4 21 product-2
50 4 20 ttttt-1-77
50 4 19 ttttt-1-77
50 4 18 product-2
50 4 17 product-2
50 4 0 zzz-w
50 5 25 zzz-w
50 5 9 ttttt-1-77
50 5 1 asdf-2
50 3 24 asdf-2
50 5 20 ttttt-1-77
50 5 0 zzz-w
file2:
============
1022 40 1 0 NO
1021 40 1 25 NO
1020 40 0 0 NO
1019 40 0 25 NO
1018 40 0 1 NO
1017 40 0 24 NO
1016 40 0 2 NO
1015 40 0 13 NO
1014 40 0 3 NO
1013 40 0 14 NO
1012 40 0 4 NO
1011 40 0 15 NO
1010 40 0 5 NO
2009 40 0 16 NO
2008 40 0 6 NO
2007 40 0 17 YES
2006 40 0 7 NO
2005 40 0 18 NO
2004 40 0 8 YES
2003 40 0 19 NO
2002 40 0 9 NO
2001 40 0 20 NO
2000 40 0 21 NO
999 40 0 11 NO
998 30 2 0 YES
997 30 2 25 NO
996 30 2 1 NO
995 30 2 24 NO
994 30 2 2 NO
993 30 2 13 NO
992 30 2 3 NO
991 30 2 14 NO
990 30 2 4 NO
989 30 2 15 NO
988 30 2 5 NO
987 30 2 16 NO
986 30 2 6 NO
985 30 2 17 NO
984 30 2 7 NO
983 30 2 18 YES
982 30 2 8 NO
981 30 2 19 NO
980 30 2 9 NO
979 30 2 20 NO
978 30 2 21 NO
977 30 2 11 NO
976 50 3 0 NO
975 50 3 25 NO
974 50 3 1 NO
973 50 3 24 NO
972 50 3 2 NO
971 50 3 13 NO
970 50 3 3 NO
969 50 3 14 NO
968 50 3 4 NO
967 50 3 15 NO
966 50 3 5 NO
965 50 3 17 NO
964 50 3 7 NO
963 50 3 18 NO
962 50 3 8 NO
961 50 3 19 NO
960 50 3 9 NO
958 50 3 10 NO
957 50 3 21 YES
956 50 3 11 NO
955 50 3 22 NO
954 50 3 12 NO
953 50 3 23 NO
952 50 4 0 YES
951 50 4 25 NO
950 50 4 1 NO
949 50 4 24 NO
948 50 4 2 NO
947 50 4 3 NO
946 50 4 17 NO
945 50 4 18 YES
944 50 4 8 NO
943 50 4 19 NO
942 50 4 9 NO
941 50 4 20 NO
940 50 4 10 NO
939 50 4 21 NO
938 50 4 11 NO
937 50 4 22 NO
936 50 5 0 NO
935 50 5 25 YES
934 50 5 1 NO
933 50 5 24 NO
932 50 5 9 NO
940 50 5 20 NO
930 50 5 10 NO
950 50 5 22 NO
928 50 5 4 NO
927 50 5 21 NO
926 50 5 14 NO
925 50 5 15 NO
924 50 5 18 NO
923 50 5 5 NO
922 50 5 7 NO
921 50 5 12 NO
920 50 5 16 NO
919 50 5 11 NO
918 50 5 8 NO
917 50 5 3 NO
916 50 5 2 NO
915 50 5 19 YES
914 50 4 14 NO
913 50 4 13 NO
Re: conditional formatting and sorting
Posted: 01 Jun 2011 08:46
by dbenham
OK, I get it now. The requirements are more complicated than I thought. But the solution is much faster than I originally thought it would be. I realized (remembered?) we can do an indexed lookup of file2 by storing the contents as variables: for each line in file2 set lookupKey=value.
I squeezed some more performance out by converting the :num2hex function into a macro. (Actually I had previously developed the macro)
I don't remember your requirements stating file1 has a header record, but your example file has one, so I programmed for it.
This should do the trick:
Code: Select all
@echo off
::Delayed expansion must be off during definition of macros
setlocal disableDelayedExpansion
set file1=file1.txt
set file2=file2.txt
set output=output.txt
set temp=%TMP%\mergeSort%random%.tmp
if exist %temp% del %temp%
if exist %output% del %output%
:: define a Line Feed (newline) string (normally only used as !LF!)
set LF=^
:: Above 2 blank lines are required - do not remove
:: define a newline with line continuation that can be referenced as %\n%
set ^"\n=^^^%LF%%LF%^%LF%%LF%^^"
:: A simple macro used to call macros with arguments
:: Usage:
::
:: %macro_call% ("arg1 arg2 arg3...") %macro.macroName%
::
set macro_Call=for /f "tokens=1-26" %%a in
:: A macro to convert a number into the hex representation of a signed 32 bit integer
:: args = num [RtnVar]
set macro.Num2Hex=do (%\n%
setlocal enableDelayedExpansion%\n%
set dec=%%~a%\n%
set /a dec=%%~a%\n%
if defined hex set "hex="%\n%
set "map=0123456789ABCDEF"%\n%
for /l %%n in (1,1,8) do (%\n%
set /a "d=dec&15,dec>>=4"%\n%
for %%d in (!d!) do set "hex=!map:~%%d,1!!hex!"%\n%
)%\n%
for %%v in (!hex!) do endlocal^&if "%%~b" neq "" (set "%%~b=%%v") else echo:%%v%\n%
)
:: Now that macros are defined we can enable delayed expansion
setlocal enableDelayedExpansion
:: Clear any existing index entries that might already exist (unlikely, but you never know)
for /f "delims==" %%v in ('2^>nul set index-') do set "%%v="
:: build an index on file2
for /f "tokens=1-4" %%A in (%file2%) do set "index-%%B %%C %%D=%%A"
:: create temp file containing file1 with indexed lookup from file2
:: prefix each line with B C D in hex to support sort operation
for /f "skip=1 tokens=1-4" %%B in (%file1%) do (
%macro_call% ("%%B hexB") %macro.Num2Hex%
%macro_call% ("%%C hexC") %macro.Num2Hex%
%macro_call% ("%%D hexD") %macro.Num2Hex%
for /f "delims=" %%v in ("index-%%B %%C %%D") do echo !hexB!,!hexC!,!hexD!,!%%v! %%B %%C %%D %%E>>%temp%
)
:: sort the temp file and create output, filling in empties as needed
set prevA=
for /f "tokens=1-3* delims=," %%a in ('sort %temp%') do (
if not defined prevA set /a "prevA=0x%%a, prevB=0x%%b, nextC=1"
set /a "A=0x%%a, B=0x%%b, C=0x%%c"
set "lastC="
if "!prevA!"=="!A!" if "!prevB!"=="!B!" if not "!nextC!"=="!C!" set /a "lastC=C-1"
if defined nextC if not "!prevA!"=="!A!" set "lastC=25"
if defined nextC if not "!prevB!"=="!B!" set "lastC=25"
if defined lastC for /l %%N in (!nextC!,1,!lastC!) do echo: !prevA! !prevB! %%N EMPTY>>%output%
echo:%%d>>%output%
set /a "prevA=A, prevB=B, nextC=C+1"
if !C! lss 25 (set /a "nextC=C+1") else set nextC=
)
if defined nextC for /l %%N in (!nextC!,1,25) do echo: !prevA! !prevB! %%N EMPTY>>%output%
del %temp%
type %output%
Here is an excerpt of the output. The lines that do not have a lookup value from file2 actually have a leading space that is stripped out by this forum:
Code: Select all
998 30 2 0 zzz-w
996 30 2 1 asdf-2
994 30 2 2 product2-TRH
992 30 2 3 product2-TRH
990 30 2 4 xyz
988 30 2 5 xyz
986 30 2 6 xyz
984 30 2 7 xyz
982 30 2 8 ttttt-1-77
980 30 2 9 ttttt-1-77
30 2 10 prdct
977 30 2 11 mmm-2
30 2 12 EMPTY
993 30 2 13 xyz
991 30 2 14 xyz
989 30 2 15 xyz
987 30 2 16 xyz
985 30 2 17 xyz
983 30 2 18 xyz
981 30 2 19 ttttt-1-77
979 30 2 20 ttttt-1-77
978 30 2 21 xyz
30 2 22 ZBM
30 2 23 ZBM
995 30 2 24 asdf-2
997 30 2 25 zzz-w
...
...
50 88 0 FFFF
50 88 1 EMPTY
50 88 2 EMPTY
50 88 3 EMPTY
50 88 4 EMPTY
50 88 5 EMPTY
50 88 6 EMPTY
50 88 7 EMPTY
50 88 8 EMPTY
50 88 9 EMPTY
50 88 10 EMPTY
50 88 11 EMPTY
50 88 12 EMPTY
50 88 13 EMPTY
50 88 14 EMPTY
50 88 15 EMPTY
50 88 16 EMPTY
50 88 17 EMPTY
50 88 18 EMPTY
50 88 19 EMPTY
50 88 20 EMPTY
50 88 21 EMPTY
50 88 22 EMPTY
50 88 23 EMPTY
50 88 24 EMPTY
50 88 25 EMPTY
Dave Benham
Re: conditional formatting and sorting
Posted: 01 Jun 2011 10:28
by dbenham
Here is an even faster version that eliminates the use of the num2hex macro. But it will not sort properly if either the cabinet number or magazine number exceeds 99. My original version properly sorts numbers up to 2 billion.
Code: Select all
@echo off
setlocal enableDelayedExpansion
set file1=file1.txt
set file2=file2.txt
set output=output3.txt
set temp=%TMP%\mergeSort%random%.tmp
if exist %temp% del %temp%
if exist %output% del %output%
:: Clear any existing index entries that might already exist (unlikely, but you never know)
for /f "delims==" %%v in ('2^>nul set index-') do set "%%v="
:: build an index on file2
for /f "tokens=1-4" %%A in (%file2%) do set "index-%%B %%C %%D=%%A"
:: create temp file containing file1 with indexed lookup from file2
:: prefix each line with B C D with leading zeros to support sort operation
for /f "skip=1 tokens=1-4" %%B in (%file1%) do (
set B=%%B
set C=%%C
set D=%%D
if !B! lss 10 set B=0!B!
if !C! lss 10 set C=0!C!
if !D! lss 10 set D=0!D!
for /f "delims=" %%v in ("index-%%B %%C %%D") do echo !B!,!C!,!D!,!%%v! %%B %%C %%D %%E>>%temp%
)
:: sort the temp file and create output, filling in empties as needed
set prevA=
for /f "tokens=1-3* delims=," %%a in ('sort %temp%') do (
set "A=%%a"
set "B=%%b"
set "C=%%c"
if "!A:~0,1!"=="0" set "A=!A:~1!"
if "!B:~0,1!"=="0" set "B=!B:~1!"
if "!C:~0,1!"=="0" set "C=!C:~1!"
if not defined prevA set /a "prevA=A, prevB=B, nextC=1"
set "lastC="
if "!prevA!"=="!A!" if "!prevB!"=="!B!" if not "!nextC!"=="!C!" set /a "lastC=C-1"
if defined nextC if not "!prevA!"=="!A!" set "lastC=25"
if defined nextC if not "!prevB!"=="!B!" set "lastC=25"
if defined lastC for /l %%N in (!nextC!,1,!lastC!) do echo: !prevA! !prevB! %%N EMPTY>>%output%
echo:%%d>>%output%
set /a "prevA=A, prevB=B, nextC=C+1"
if !C! lss 25 (set /a "nextC=C+1") else set nextC=
)
if defined nextC for /l %%N in (!nextC!,1,25) do echo: !prevA! !prevB! %%N EMPTY>>%output%
del %temp%
type %output%
Dave Benham
Re: conditional formatting and sorting
Posted: 01 Jun 2011 23:56
by Amin
Dear Dave Benham
I tried it just now, it is working ! i thought it is not doable,but you are an expert.
Thank you very much ,I really appreciate your help.
Re: conditional formatting and sorting
Posted: 20 Jun 2011 05:12
by Amin
Hi Dbenham
today i noticed something,if the SLOT No 0 does not exist the sorting will start from the first smallest SLOT number until 25 .
example
If
cab MAG SL
30 15 3
30 15 5
30 15 8
...
but the cabinet 30, magazine 15 ,does not have a slot No 0 in the output,then the sorting will start from 3(the smallest SLOT number available) , it does not start from 0.
Where ,what we need is to assume that slots(from 0- to 25) should be available.
Can you please make this amendment?
Thank you..
Re: conditional formatting and sorting
Posted: 20 Jun 2011 16:14
by dbenham
This will eliminate gaps when 0 slot is missing:
Code: Select all
@echo off
::Delayed expansion must be off during definition of macros
setlocal disableDelayedExpansion
set file1=file1.txt
set file2=file2.txt
set output=output.txt
set temp=%TMP%\mergeSort%random%.tmp
if exist %temp% del %temp%
if exist %output% del %output%
:: define a Line Feed (newline) string (normally only used as !LF!)
set LF=^
:: Above 2 blank lines are required - do not remove
:: define a newline with line continuation that can be referenced as %\n%
set ^"\n=^^^%LF%%LF%^%LF%%LF%^^"
:: A simple macro used to call macros with arguments
:: Usage:
::
:: %macro_call% ("arg1 arg2 arg3...") %macro.macroName%
::
set macro_Call=for /f "tokens=1-26" %%a in
:: A macro to convert a number into the hex representation of a signed 32 bit integer
:: args = num [RtnVar]
set macro.Num2Hex=do (%\n%
setlocal enableDelayedExpansion%\n%
set dec=%%~a%\n%
set /a dec=%%~a%\n%
if defined hex set "hex="%\n%
set "map=0123456789ABCDEF"%\n%
for /l %%n in (1,1,8) do (%\n%
set /a "d=dec&15,dec>>=4"%\n%
for %%d in (!d!) do set "hex=!map:~%%d,1!!hex!"%\n%
)%\n%
for %%v in (!hex!) do endlocal^&if "%%~b" neq "" (set "%%~b=%%v") else echo:%%v%\n%
)
:: Now that macros are defined we can enable delayed expansion
setlocal enableDelayedExpansion
:: Clear any existing index entries that might already exist (unlikely, but you never know)
for /f "delims==" %%v in ('2^>nul set index-') do set "%%v="
:: build an index on file2
for /f "tokens=1-4" %%A in (%file2%) do set "index-%%B %%C %%D=%%A"
:: create temp file containing file1 with indexed lookup from file2
:: prefix each line with B C D in hex to support sort operation
for /f "skip=1 tokens=1-4" %%B in (%file1%) do (
%macro_call% ("%%B hexB") %macro.Num2Hex%
%macro_call% ("%%C hexC") %macro.Num2Hex%
%macro_call% ("%%D hexD") %macro.Num2Hex%
for /f "delims=" %%v in ("index-%%B %%C %%D") do echo !hexB!,!hexC!,!hexD!,!%%v! %%B %%C %%D %%E>>%temp%
)
:: sort the temp file and create output, filling in empties as needed
set "nextC=0"
for /f "tokens=1-3* delims=," %%a in ('sort %temp%') do (
set /a "A=0x%%a, B=0x%%b, C=0x%%c"
set "lastC="
if "!nextC!" neq "0" if "!prevA!" neq "!A!" set "lastC=25"
if "!nextC!" neq "0" if "!prevB!" neq "!B!" set "lastC=25"
if defined lastC (
for /l %%N in (!nextC!,1,!lastC!) do echo: !prevA! !prevB! %%N EMPTY>>%output%
set "nextC=0"
)
if "!nextC!" neq "!C!" (
set /a "lastC=C-1"
for /l %%N in (!nextC!,1,!lastC!) do echo: !A! !B! %%N EMPTY>>%output%
)
echo:%%d>>%output%
set /a "prevA=A, prevB=B, nextC=(C+1)%%26"
)
if "!nextC!" neq "0" for /l %%N in (!nextC!,1,25) do echo: !prevA! !prevB! %%N EMPTY>>%output%
del %temp%
type %output%
Dave Benham
Re: conditional formatting and sorting
Posted: 20 Jun 2011 23:45
by Amin
Dear Dbenham
I tried it and it is working perfectly now, I appreciate it.Thank you very much!