Batch file to join and re-format lines

Question

I am trying to write a batch file that will process a file with records like this:

c:\SSSM\logs\CAP61.K10TEST.LOAD.log:  299 Rows successfully loaded.
c:\SSSM\logs\CAP61.K10TEST.LOAD.log:  0 Rows not loaded due to data errors.
c:\SSSM\logs\CAP63.K10TEST.LOAD.log:  232 Rows successfully loaded.
c:\SSSM\logs\CAP63.K10TEST.LOAD.log:  0 Rows not loaded due to data errors.

And create a delimited file that looks like this (with no blank lines in between):

CAP61-LOADED; 299

CAP61-NOT-LOADED; 0

CAP63-LOADED; 232

CAP63-NOT-LOADED; 0

The directory and the log file names could change, so I think I need to do some kind of search on the table prefix (CAP, CFI, etc.) to extract the 5 byte table name, then format one kind of tag if the lines contains "successfully loaded" and the other if it contains "not loaded". I would appreciate any help you can offer.

score 1 · Answer 1 · answered Jan 15 '14 at 05:35

@ECHO OFF
SETLOCAL
(
 FOR /f "delims=" %%a IN (q21129289.txt) DO (
  ECHO %%a|FIND /i "not loaded" >NUL
  IF ERRORLEVEL 1 (SET "loaded=") ELSE (SET "loaded=-NOT")
  FOR /f "tokens=2,3delims=:" %%c IN ("%%a") DO (
   FOR /f "delims=." %%n IN ("%%~nc") DO CALL :showresult %%n %%d
  )
 )
)>newfile.txt

GOTO :EOF

:showresult
ECHO %1%loaded%-LOADED; %2
GOTO :eof

This batch reads from the file q21129289.txt and creates a new file newfile.txt

Parenthesising the mainline causes the output to be redirected to the file; > creates anew, >> would append.

First, the entire line is applied to %%a. This is sent to FIND which looks for not loaded (the /i means case-insensitive)

If that string is NOT found, errorlevel is set to non-zero and therefore loaded is set to an empty string. If the string is found, errorlevel is set to 0 and loaded therefore is set to -NOT.

Taking %%a as a string, we look for the second and third tokens delimited by a colon. These are applied to %%c and %%d (the next metavariable, alphabetically) so the full-filename minus the drive goes to %%c and the entire string after the second colon to %%d

Next step is to pull the same trick on the name-part only of the filename in %%c. This neatly disposes of the path, and we want the first token given a delimiter of . of the rest. This is applied to %%n

The CALL then invokes the subroutine, providing the required first-part-of-the-name and remainder-of-the-line-after-the-original-second-colon` as parameters; eg

call :showresult CAP61  299 Rows successfully loaded.

The subroutine simply strings together the first parameter (CAP61), the contents of loaded, the string -LOADED; and the first string from the end-of-line chunk (299)

The output of course is redirected to the output file.

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

The wonders of regular expressions :-)

The following uses REPL.BAT - a hybrid JScript/batch utility that performs a regex search and replace on stdin and writes the result to stdout. REPL.BAT should be in your current directory, or better yet, somewhere within your PATH.

type input.txt|repl ".*\\(.*?)\..*: *(\d*) .*? (not )?loaded.*" "$1-$3LOADED; $2"|repl "-not " "-NOT-" >output.txt

edited May 23 '17 at 10:33

Community

1
1

answered Jan 15 '14 at 06:19

dbenham

127,446
28
251
390

+1 That's clever. How does the ? make the match not greedy? A ? matches zero or 1 of the previous term - but if the previous term is .* then I can't see how it returns a non-greedy match. Can you please explain? – foxidrive Jan 15 '14 at 07:48
@foxidrive - The [JScript regex doc link](http://msdn.microsoft.com/en-us/library/ae5bf541(v=vs.80).aspx) embedded in the REPL.BAT script documents all the regex meta characters available to JScript, including `?` for non-greedy match. `?` has 5 different uses, depending on context. – dbenham Jan 15 '14 at 12:52
Thanks Dave - I actually checked that documentation but in my experience it's not normal to see a character being used for multiple purposes, and in different parts of the document - so I didn't go past the first reference. I see now where it is, so thanks. We live and learn. – foxidrive Jan 15 '14 at 13:02

MC ND · Answer 3 · 2014-01-15T10:43:38.790

For an exercise on splitting and string substitution

@echo off
    setlocal enableextensions enabledelayedexpansion

    set "successfully="    
    set "not=-NOT"

    for /f "tokens=2,3 delims=:" %%a in (input.txt) do (
        for /f "tokens=1 delims=." %%c in ("%%~na") do (
            for /f "tokens=1,3 delims= " %%d in ("%%b") do echo %%c!%%e!-LOADED; %%d
        )
    )
    endlocal

for lines as

c:\SSSM\logs\CAP61.K10TEST.LOAD.log:  299 Rows successfully loaded.
c:\SSSM\logs\CAP61.K10TEST.LOAD.log:  0 Rows not loaded due to data errors.

we get

%%a = \SSSM\logs\CAP61.K10TEST.LOAD.log
%%b = 299 Rows successfully ... / 0 Rows not loaded ...
%%c = CAP61
%%d = 299 / 0
%%e = successfully / not

First for splits on colon to separate the file reference %%a from informational data %%b

From the file reference in %%a, get only the file name and split on dots to get the required part in %%c.

From the informational data in %%b, split on spaces to retrieve the row count in %%d and the status in %%e

Now that we have all the required information, the rest is formatting output. The two defined variables (not and successfully) are expanded depending of the content of %%e to get the correct output.

+1, I particularly like the elegance of the not/successfully translation. — dbenham, Jan 15 '14 at 13:05

score 0 · Answer 4 · answered Jan 15 '14 at 13:12

0

you can use awk. download from here

C:\> awk "{s=$2; gsub(/.*\\|\..*$/,\"\") ;print $0\" \"( (s>0) ?\"LOADED\":\"NOT-LOADED\" ) }" myFile.txt
CAP61 LOADED
CAP61 NOT-LOADED
CAP63 LOADED
CAP63 NOT-LOADED

answered Jan 15 '14 at 13:12

brianadams

233
1
5

Batch file to join and re-format lines

4 Answers4