Gfrktyl

I am new to batch-program and my wife asked me to help her to speed up her load. Question is, how will I extract the business date, terminal num(00099106), transaction amount, and commission from this sample text file?

[4w                                                    IST POS TERMINAL ACTIVITY REPORT

                                                  FOR CREDIT ACCOUNT NUMBER: 1120162163

Report No.: BDO030114                                                                                                              Page 1

Business Date: 03/02/2015                                                                                      Date Generated: 03/03/2015

=========================================================================================================================================

   TRANSACTION   |    TERMINAL    |      |        CARD        |      ACCOUNT       |   TRANSACTION   |RESULT|AUTH. |REV|MSG |  MERCHANT  

  DATE  |  TIME  |   NO.  |RECEIPT|BR NO.|       NUMBER       |        NO.         |CODE|   AMOUNT   | CODE |METHOD|IND|TYPE|   SHARE    

=========================================================================================================================================

03/01/15 23:54:30 00099106     203  00695 6018530000004863     ************          00      2,250.00   0      1        210               

               ===================================================================================================

                                   | CARD PRODUCT  | COUNT |       AMOUNT       |  COMMISSION  |

               ===================================================================================================

                                       00099106 SMARTELLER            1             2,250.00                

                                00099106 TOTAL:                       1             2,250.00                



                                       MERCHANT SMARTELLER            1             2,250.00          33.75 

                              MERCHANT   TOTAL:                       1             2,250.00          33.75 



                            TOTAL FOR PURCHASE:                       1             2,250.00          33.75 



                            TOTAL PER MERCHANT:                       1             2,250.00          33.75 



[4w

The output should be like this

03/02/2015;00099106;2,250.00;33.75

Regardless what programming language or tool you use, you must first establish how you will reliably identify the information you need to extract.

It looks to me like you your date follows "Business Date: " at the beginning of a line.

The terminal number follows "nn/nn/nn nn:nn:nn " (date and time) at the beginning of a line.

You are not clear as to which amount or commission you want. I'll assume you want the values that follow "TOTAL PER MERCHANT:"

It is fairly easy to get the information using pure batch.

For this example I'm assuming the text is in a file named test.txt.

The FINDSTR command can look for the 3 lines containing your data using primitive regular expressions. The /R option treats the searches as regular expressions. I use a separate /C:"search string" option to specify each search string that contains spaces. When looking for the line beginning with a date and time, I opted to simply look for the slashes and colons with the proper number of characters between.

The FOR /F command parses the three lines of FINDSTR output, one line at a time. By default, it treats spaces and tabs as token delimiters. A string of consecutive spaces is treated as a single delimiter. So both the date and the terminal number are the 3rd token on the line. Your amount and commission are the 5th and 6th tokens on the line.

So I explicitly ask for the 3rd, 5th, and 6th tokens of each line, specifying %%A as the first captured token. This means the 3rd token will be in %%A, the 5th in %%B, and the 6th in %%C.

I explicitly clear the dt and term variables. The lines always appear in the same order, so I simply check if dt is defined. If not, then it must be the first line and I set the date value. Otherwise I check if term is defined. If not, then it must be the second line and I set the terminal value. Otherwise it must be the 3rd line and I set the amount and commission values.

@echo off

setlocal

set "dt="

set "term="

for /f "tokens=3,5,6" %%A in (

  'findstr /rc:"^Business Date:" /c:"^../../.. ..:..:.." /c:"TOTAL PER MERCHANT:" test.txt'

) do (

  if not defined dt (

    set "dt=%%A"

  ) else if not defined term (

    set "term=%%A"

  ) else (

    set "amt=%%B"

    set "comm=%%C"

  )

)

echo %dt%;%term%;%amt%;%comm%

This problem was not particularly difficult to solve using pure batch, but often times batch is ill suited for text processing. It often times requires lots of arcane code, and/or it may be slow.

I have written JREPL.BAT - a hybrid JScript/batch utility that can very efficiently perform sophisticated regular expression find/replace operations on text. JREPL.BAT is pure script that runs natively on any Windows machine from XP onward. Full documentation is embedded within the utility. It can be accessed by issuing jrepl /?. You can use jrepl /?|more if you want to see the help one page at a time.

Assuming you have JREPL.BAT somewhere within your PATH, then the following will extract the information:

@echo off

call jrepl "[sS]*nBusiness Date: (.*?) (?:.*n){5}.*? .*? (.*?) [sS]*?TOTAL PER MERCHANT: +.*? +(.*?) +([^ ]*)[sS]*" "$1;$2;$3;$4" /m /f test.txt

echo(

convert.cmd:

powershell .convert.ps1

powershell convert.ps1:

$head=3

$f=gc data.txt

$f[$head..($f.count-1)]|%{

if($_.Contains("Business Date:"   )){$s=$_.split(': ')[3]};

if($_.Contains("MERCHANT   TOTAL:")){$MT=($_.trim()-replace's+',' ').split(' ');$s=$s+';'+$MT[-2]+';'+$MT[-1]};

if($_.Contains("SMARTELLER"       )){$SM=($_.trim()-replace's+',' ').split(' ');if($SM[0] -ne "MERCHANT"){$s=$s+';'+$SM[0]} };

} 

$s|ac short.txt

Var 2:

$f=gc data.txt

$s=$f[(3)].Substring(15,10)+';'+$f[(8)].Substring(18,8)+';'

$head=15

$f[$head..($f.count-1)]|%{if($_.Contains("MERCHANT   TOTAL:")){$MT=($_.trim()-replace's+',' ').split(' ');$s=$s+$MT[-2]+';'+$MT[-1]}} 

$s|ac short.txt

Var 3:

$f=gc data.txt

$s=$f[(3)].Substring(15,10)+';'+$f[(8)].Substring(18,8)+';'+($f[(16)].Substring(80,15)).Trim()+';'+$f[(16)].Substring(102,5)|ac short.txt