Find missing items in a list of element

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
darioit
Posts: 230
Joined: 02 Aug 2010 05:25

Find missing items in a list of element

#1 Post by darioit » 28 Nov 2020 02:20

Hello,
here I'm again to ask for your help about a problem that I wouldn't like to solve with many excel steps, but i'm too jackass to do by myself a correct script, I'm sure you be in my help :D

The problem in very easy, find missing elements in a list of number, for example in this list below number 000002, 000004 and 000005 are missing, but sometimes this list are not sorted and also often I dont know which is first and last list number, for example

Input file:
field1;field2;aaaammggXX19000003;text;data
field1;field2;aaaammggXX19000006;text;data
field1;field2;aaaammggXX19000001;text;data

Desired Result:
XX19000002
XX19000004
XX19000005

Position of numbers are always in same column 26,6

Thank you as always in advance
Dario

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

Re: Find missing items in a list of element

#2 Post by aGerman » 28 Nov 2020 06:04

Position of numbers are always in same column 26,6
Sort the content beginning at the 27th character in each row. Extract the desired 6 digits and prepend a 1 to make it a decimal number. Always save the last row you computed to run a FOR /L loop for the missing numbers.

Code: Select all

@echo off &setlocal DisableDelayedExpansion
set "src=input.csv"
set "dst=missing.txt"

set "last="
>"%dst%" (
  for /f "delims=" %%i in ('sort /+27 "%src%"') do (
    if not defined last (
      set "last=%%i"
    ) else (
      set "row=%%i"
      setlocal EnableDelayedExpansion
      set /a "nxt=1!last:~26,6!+1,bfr=1!row:~26,6!-1"
      for /l %%j in (!nxt! 1 !bfr!) do (
        set "missing=%%j"
        echo XX19!missing:~1!
      )
      endlocal
      set "last=%%i"
    )
  )
)
Steffen

darioit
Posts: 230
Joined: 02 Aug 2010 05:25

Re: Find missing items in a list of element

#3 Post by darioit » 28 Nov 2020 09:42

many thanks! Works great as usual !

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

Re: Find missing items in a list of element

#4 Post by Aacini » 28 Nov 2020 14:07

Code: Select all

@echo off
setlocal EnableDelayedExpansion

rem Load numbers in a list and get the max
set "list= "
set "max=0"
for /F %%a in (input.txt) do (
   set "line=%%a"
   set /A "num=1!line:~26,6!-1000000"
   set "list=!list!!num! "
   if !num! gtr !max! set "max=!num!"
)

rem Show missing numbers from 1..max
for /L %%i in (1,1,%max%) do (
   if "!list: %%i =!" equ "%list%" (
      set /A "num=1000000+%%i"
      echo XX19!num:~1!
   )
)
Antonio

Post Reply