Find, replace and move rows with matches and mismatches

[plasma33]

Hi guys,

I have a csv/text file with a bunch of numbers ranging from 500 to 500,000 and would like to replace the matches with a and mismatches by shifting the mismatched content to right simultaneously shifting the rest of the columns to right but leaving the top row untouched and then replacing the empty spaces in that column with b so that I have a fixed length matrix, for instance, I have the following:

Code: Select all

12345,6457,789,21231,657
6457,21231,657
12345,789,21231

When I search for the string 12345 in the first column replace the matched cells (row 1 column 1 and row 3 column 1) with 1 and mismatched cell (row 2 column 1) with a 0 and comma after pushing the second row to the right..see below for the output:

Code: Select all

a,6457,789,21231,657
b,6457,21231,657
a,789,21231

Now when I search for the next string (6457) in the second column perform the same steps as above, that is, replace the matched cells (row 1 column 2 and row 2 column 2) with 1 and mismatched cell (row 3 column 2) with a 0 and comma after pushing the third row to the right ..see below for the output:

Code: Select all

a,a,789,21231,657
b,a,21231,657
a,b,789,21231

The final output after looking for 789, 21231 and then 657 should look something as below (a fixed length matrix):

Code: Select all

a,a,a,a,a
b,a,b,a,a
a,b,a,a,b,

See below for a link to the sample file:
https://pastebin.com/AmbHYC9T

Thanks
Plasma33

[Aacini]

Your "question" (a task request, really) have a lot of confusing or incomplete details. I strongly suggest you to carefully read the first sticky post of this forum...

However, this code works:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set n=0
for /F "delims=" %%a in (input.txt) do (
   set /A n+=1, i=0
   for %%b in (%%a) do (
      set /A i+=1
      set "cell[!n!][!i!]=%%b"
   )
   set /A "len[!n!]=i"
)

set i=0
:nextMatch
set /A i+=1
for %%i in (%i%) do (
   for /L %%n in (1,1,%n%) do (
      if "!cell[%%n][%%i]!" equ "%1" (
         set "cell[%%n][%%i]=a"
      ) else (
         for /L %%j in (!len[%%n]!,-1,%%i) do (
            set /A jP1=%%j+1
            set "cell[%%n][!jP1!]=!cell[%%n][%%j]!"
         )
         set "cell[%%n][%%i]=b"
         set /A "len[%%n]+=1"
      )
   )
)
shift
if "%~1" neq "" goto nextMatch

for /L %%n in (1,1,%n%) do (
   set "line=!cell[%%n][1]!"
   for /L %%j in (2,1,!len[%%n]!) do set "line=!line!,!cell[%%n][%%j]!"
   echo(!line!
)

Output example:

Code: Select all

> test.bat 12345
a,6457,789,21231,657
b,6457,21231,657
a,789,21231

> test.bat 12345 6457
a,a,789,21231,657
b,a,21231,657
a,b,789,21231

> test.bat 12345 6457 789 21231 657
a,a,a,a,a
b,a,b,a,a
a,b,a,a,b

Antonio

[plasma33]

Hi Antonio,
My apologies for providing incomplete or confusing details. I have now given a better description of what I want and a link to a sample file. However the code that you supplied worked like a charm and doing exactly what I asked for. Much appreciated for this. Is it possible to modify the code so that it runs a bit faster. I have a file which is around 400kb and around 500 strings to search. For privacy reasons I can't share the file.
Thanks
Plasma33

[Aacini]

This new version should run faster:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

rem Load strings to search from parameters (modify this point if required)
set n=0
for %%a in (%*) do (
   set /A n+=1
   set "S!n!=%%a"
)

(for /F "tokens=1* delims=," %%a in (input.txt) do (
   set "this=%%a" & set "rest=%%b"
   set "out="
   for /L %%i in (1,1,%n%) do if defined this (
      if !this! equ !S%%i! (
         set "out=!out!,a"
         for /F "tokens=1* delims=," %%x in ("!rest!") do set "this=%%x" & set "rest=%%y"
      ) else (
         set "out=!out!,b"
      )
   )
   REM IMPORTANT: Next IF is useful just for *partial searchs*. Remove it when complete searchs are done.
   if defined this (
      set "out=!out!,!this!"
      if defined rest set "out=!out!,!rest!"
   )
   echo(!out:~1!
)) > output.txt

Please, report the timing of both versions...

Antonio

[plasma33]

Hi Antonio,

The second code that you supplied worked like a breeze. It took around 20 seconds to generate the output. However the first code didn't succeed in generating the output. It ran for around 6-7 hours but didn't generate any output. I had to break the file into several smaller files in order to make it work. Many thanks for all your help.

Plasma33