Output numbers not in list?

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

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

Output numbers not in list?

#1 Post by SIMMS7400 » 27 Apr 2017 08:43

Hi Team -

I'm not sure if batch has this capability, but I'm looking take the list (below) and run a script over it that reads the list and output any numbers not in the list to another file.

I assume the CC_D_ would need to be stripped off first. Also, the MAX numerical value is CC_D_99999.

Is this possible?
Thanks!


Code: Select all

"Name"

"CC_D_00485"
"CC_D_00490"
"CC_D_00488"
"CC_D_00489"
"CC_D_00486"
"CC_D_00491"
"CC_D_00487"
"CC_D_00016"
"CC_D_00017"
"CC_D_00036"
"CC_D_00010"
Top
penpen
Expert
Posts: 2009
Joined: 23 Jun 2013 06:15
Location: Germany

Re: Output numbers not in list?

#2 Post by penpen » 27 Apr 2017 09:28

If i understand it right, then you should be able to solve that using findstr:

Code: Select all

@echo off
setlocal enableExtensions disableDelayedExpansion
:: building the example files "list1.txt" and "list2.txt"
(
   echo("Name"
   echo(
   echo("CC_D_00485"
   echo("CC_D_00490"
   echo("CC_D_00488"
   echo("CC_D_00489"
   echo("CC_D_00486"
   echo("CC_D_00491"
   echo("CC_D_00487"
   echo("CC_D_00016"
   echo("CC_D_00017"
   echo("CC_D_00036"
   echo("CC_D_00010"
) >"list1.txt"
(
   echo("Name"
   echo(
   echo("CC_D_00486"
   echo("CC_D_00491"
   echo("CC_D_00489"
   echo("CC_D_00490"
   echo("CC_D_00487"
   echo("CC_D_00492"
   echo("CC_D_00488"
   echo("CC_D_00017"
   echo("CC_D_00018"
   echo("CC_D_00037"
   echo("CC_D_00011"
)>"list2.txt"

:: display all in "list1.txt", but not in "list2.txt"
findstr /V /G:"list2.txt" "list1.txt"
echo(

:: display all in "list2.txt", but not in "list1.txt"
findstr /V /G:"list1.txt" "list2.txt"
echo(

goto :eof
You may filter out the empty word first or last (if you only want to list the lines with numbers).

penpen
Top
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Re: Output numbers not in list?

#3 Post by SIMMS7400 » 27 Apr 2017 09:42

Hi PenPen -

I dont think I was clear enough.

Right now, there exists 11 numerical strings (if you strip CC_D_ off)...

Therefore, I'd want the output to list the remaining 99988 numbers...

Does that make sense? And if so, if that even possible?

And all strings be inclusive of 5 number digits...For instance 1 is actual 00001...

Thanks!
Top
penpen
Expert
Posts: 2009
Joined: 23 Jun 2013 06:15
Location: Germany

Re: Output numbers not in list?

#4 Post by penpen » 27 Apr 2017 10:52

The files have a slightly different format, but the solution is the same:

Code: Select all

@echo off
setlocal enableExtensions disableDelayedExpansion
:: building the example files "list1.txt" and "list2.txt"
(
   echo("Name"
   echo(
   echo("CC_D_00485"
   echo("CC_D_00490"
   echo("CC_D_00488"
   echo("CC_D_00489"
   echo("CC_D_00486"
   echo("CC_D_00491"
   echo("CC_D_00487"
   echo("CC_D_00016"
   echo("CC_D_00017"
   echo("CC_D_00036"
   echo("CC_D_00010"
) >"list1.txt"

<nul (
   for /F "usebackq tokens=3 delims=_"  %%a in ("list1.txt") do (
      set /P "=%%~a
      echo(
   )
) >"list1.nums.txt"

if not exist "allNumbers.txt" (
   for /L %%a in (    0, 1,     9) do echo(0000%%~a
   for /L %%a in (   10, 1,    99) do echo(000%%~a
   for /L %%a in (  100, 1,   999) do echo(00%%~a
   for /L %%a in ( 1000, 1,  9999) do echo(0%%~a
   for /L %%a in (10000, 1, 99999) do echo(%%~a
) >"allNumbers.txt"

>"result.txt" findstr /V /G:"list1.nums.txt" "allNumbers.txt"

goto :eof


penpen

Edit: I've removed the unneccassary " # %%~b # %%~c" in "set /P "=%%~a # %%~b # %%~c".
Top
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Re: Output numbers not in list?

#5 Post by SIMMS7400 » 27 Apr 2017 12:56

Wow PenPen, this works absolutely fantastically!!! Thank you!

Code: Select all

setlocal enableExtensions disableDelayedExpansion

PUSHD %LOCALEXPORTPATH%

<nul (
   for /F "usebackq tokens=3 delims=_"  %%a in ("CC_D_Members.csv") do (
      set /P "=%%~a # %%~b # %%~c
      echo(
   )
) >"list1.nums.txt"

if not exist "allNumbers.txt" (
   for /L %%a in (    0, 1,     9) do echo(CC_D_0000%%~a
   for /L %%a in (   10, 1,    99) do echo(CC_D_000%%~a
   for /L %%a in (  100, 1,   999) do echo(CC_D_00%%~a
   for /L %%a in ( 1000, 1,  9999) do echo(CC_D_0%%~a
   for /L %%a in (10000, 1, 99999) do echo(CC_D_%%~a
) >"allNumbers.txt"

>>"DRM_TBC_Codes.csv" findstr /V /G:"list1.nums.txt" "allNumbers.txt"

FOR %%F IN ( result allNumbers list1.nums ) DO ECHO Y | DEL %%F.txt

POPD




I have it set up in my environment now and it's running as expected! I did however have to create a new export from my source system to get me just CC_D* members. But, I currently have an export already part of a process that includes CC_D* members in column 2. Would we be able to extract the CC_D member from this file instead?

Here is a sample file:

Code: Select all

"Parent Node","Primary Node Name","Alias","CC-DummyNode","HFM_NonPersonnel_Acct","Mgmt_Unit1","Weight","ASC_CMC_MODEL_I","ASC_DEV_MODEL_I","ASC_GAO_MODEL_I","ASC_PRD_MODEL_I","Country","Entity","Comp_Code","TM1_App","Source","Abbreviation"

"CC90740","CC_D_00485","CNS Insights USTP003 (Dummy)","1","PE310106","MU_IM","","","","","","US HM_DI","USTP003","US08","FIT4PLAN","",""
"CC90740","CC_D_00490","CNS Insights JPTP001 (Dummy)","1","PE310106","MU_IM","","","","","","JP HM_DI","JPTP001","2780","FIT4PLAN","",""
"CC90543","CC90257","Specialties Mgmt","0","","MU_IM","","","","","","","","","","",""
"CC90543","CC90258","Specialities Legacy","0","","MU_IM","","","","","","","","","","",""
"CC90632","CC90271","GI TA","0","","MU_IM","","","","","","","","","","",""
"CC90271","CC90272","GI Mgmt","0","","MU_IM","","","","","","","","","","",""
"CC90271","CC90274","Entyvio","0","","MU_IM","","","","","","","","","","",""
"CC90274","CC90273","Entyvio personnel","0","","MU_IM","","","","","","","","","","",""
"CC90271","CC90275","GI NPL","0","","MU_IM","","","","","","","","","","",""
"CC90271","CC90276","GI Legacy","0","","MU_IM","","","","","","","","","","",""
"CC90271","CC90741","GI Insights","0","","MU_IM","","","","","","","","","","",""
"CC90741","CC_D_00488","GI Insights USTP003 (Dummy)","1","PE310106","MU_IM","","","","","","US HM_DI","USTP003","US08","FIT4PLAN","",""
"CC90741","CC_D_00489","GI Insights JPTP001 (Dummy)","1","PE310106","MU_IM","","","","","","JP HM_DI","JPTP001","2780","FIT4PLAN","",""
"CC90271","CC90252","Alofisel / Vonoprazan","0","","MU_IM","","","","","","","","","","",""
"CC90632","CC90281","Global Commercial Management","0","","MU_IM","","","","","","","","","","",""
"CC90281","CC90283","GC Management","0","","MU_IM","","","","","","","","","","",""
"CC90281","CC90284","GM Management - old","0","","MU_IM","","","","","","","","","","",""
"CC90632","CC90743","Market Data Management","0","","MU_IM","","","","","","","","","","",""
"CC90743","CC_D_00486","KM JPTP001 (Dummy)","1","PE310106","MU_IM","","","","","","JP HM_DI","JPTP001","2780","FIT4PLAN","",""
"CC90743","CC_D_00491","KM USTP001 (Dummy)","1","PE310106","MU_IM","","","","","","US HM_DI","USTP001","US01","FIT4PLAN","",""
"CC90744","CC_D_00487","LRF JPTP001 (Dummy)","1","PE310106","MU_IM","","","","","","JP HM_DI","JPTP001","2780","FIT4PLAN","",""
"CC90632","CC90680","LTIP Global Commercial","0","","MU_IM","","","","","","","","","","",""
"CC90632","CC90744","LRF","0","","MU_IM","","","","","","","","","","",""
"CC90680","CC_D_00016","LTIP GC CHLE001 (Dummy)","1","PE330070","MU_IM","","","","","","CH HM_DI","CHLE001","","FIT4PLAN","",""
"CC90680","CC_D_00017","LTIP GC Singapore CHLE001 (Dummy)","1","PE330070","MU_IM","","","","","","CH HM_DI","CHLE001","","FIT4PLAN","",""
"CC90680","CC_D_00036","LTIP GC USTP002 (Dummy)","1","PE330070","MU_IM","","","","","","US HM_DI","USTP002","US08","FIT4PLAN","",""


Thanks!
Top
penpen
Expert
Posts: 2009
Joined: 23 Jun 2013 06:15
Location: Germany

Re: Output numbers not in list?

#6 Post by penpen » 27 Apr 2017 15:54

I had a slight error in the code above (which should not produce any error (but who knows).

If you only want to extract the number from the "CC_D_[0-9][0-9][0-9][0-9][0-9]" in the second row:

Code: Select all

@echo off
setlocal enableExtensions disableDelayedExpansion

:: shorten prebuilt "list.txt" to "list1.txt" which only contains the "CC_D_[0-9][0-9][0-9][0-9][0-9]" at position 2:
> "list1.txt" findstr /R /C:"^\"[^^\"]*\",\"CC_D_[0-9][0-9][0-9][0-9][0-9]\"," "list.txt"

:: extract second row entry:
(
   for /F "usebackq tokens=2 delims=,"  %%a in ("list1.txt") do echo(%%~a
) >"list1.row2.txt"

:: extract single numbers:
(
   for /F "usebackq tokens=3 delims=_"  %%a in ("list1.row2.txt") do echo(%%~a
) >"list1.nums.txt"

if not exist "allNumbers.txt" (
   for /L %%a in (    0, 1,     9) do echo(0000%%~a
   for /L %%a in (   10, 1,    99) do echo(000%%~a
   for /L %%a in (  100, 1,   999) do echo(00%%~a
   for /L %%a in ( 1000, 1,  9999) do echo(0%%~a
   for /L %%a in (10000, 1, 99999) do echo(%%~a
) >"allNumbers.txt"

findstr /V /G:"list1.nums.txt" "allNumbers.txt" > "result.txt"

goto :eof


penpen
Top
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Re: Output numbers not in list?

#7 Post by SIMMS7400 » 27 Apr 2017 17:16

Hi PenPen -

Thank you again!

here is the solution I came up with, but yours is a touch cleaner. I will use yours.

Code: Select all

for /F "usebackq skip=2 tokens=2 delims=," %%a in ("organization_cc.csv") do ECHO %%a>>1.csv
for /F %%b in ('find "CC_D_" ^< "1.csv" ') do echo %%b>>2.csv

<nul (
   for /F "usebackq tokens=3 delims=_"  %%a in ("2.csv") do (
      set /P "=%%~a # %%~b # %%~c
      echo(
   )
) >"list1.nums.txt"

if not exist "allNumbers.txt" (
   for /L %%a in (    0, 1,     9) do echo(CC_D_0000%%~a
   for /L %%a in (   10, 1,    99) do echo(CC_D_000%%~a
   for /L %%a in (  100, 1,   999) do echo(CC_D_00%%~a
   for /L %%a in ( 1000, 1,  9999) do echo(CC_D_0%%~a
   for /L %%a in (10000, 1, 99999) do echo(CC_D_%%~a
) >"allNumbers.txt"

>>"DRM_TBC_Codes.csv" findstr /V /G:"list1.nums.txt" "allNumbers.txt"

TIMEOUT /T 5 >nul

FOR %%F IN ( allNumbers.txt list1.nums.txt 1.csv 2.csv ) DO DEL "%%F" >nul


Thank you again!!
Top
Post Reply

Return to “DOS Batch Forum”