Find, replace and move rows with matches and mismatches

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
plasma33
Posts: 22
Joined: 26 Jul 2017 21:18

Find, replace and move rows with matches and mismatches

#1 Post by plasma33 » 07 Aug 2019 18:46

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
Last edited by plasma33 on 08 Aug 2019 12:04, edited 1 time in total.

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

Re: Find, replace and move rows with matches and mismatches

#2 Post by Aacini » 07 Aug 2019 22:53

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
Posts: 22
Joined: 26 Jul 2017 21:18

Re: Find, replace and move rows with matches and mismatches

#3 Post by plasma33 » 08 Aug 2019 12:11

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
Expert
Posts: 1615
Joined: 06 Dec 2011 22:15
Location: México City, México

Re: Find, replace and move rows with matches and mismatches

#4 Post by Aacini » 09 Aug 2019 08:19

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
Posts: 22
Joined: 26 Jul 2017 21:18

Re: Find, replace and move rows with matches and mismatches

#5 Post by plasma33 » 09 Aug 2019 16:42

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

Post Reply