search and get the values into new text file

[daillest319]

I'm trying to capture specfic strings from a text file and place them in an excel workbook. I need the payment and the chk number side by side so payment in column A and chk num that corresponds to the payment  in column B if possbile if not a txt file will be ok. The code i m using now which is below just puts them in a new text file and its not excactly how i want to the file to output. This is what i need from the file.

1. payment total and the amount anything after in that line is not needed
2. CHECK/EFT NUMBER and chk noanything after in that line is not needed

ex.
payment total  21115.36
CHECK/EFT NUMBER: 0000011111111

Code: [Select]


find /i "CHECK/EFT NUMBER" "C:\test\New Folder\*.txt" >> "C:\test\New Folder\log.txt"

find /i "PAYMENT TOTAL:" "C:\test\New Folder\*.txt" >> "C:\test\New Folder\log.txt"




[recovering disk space, attachment deleted by admin]

[Squashman]

Code: [Select]

@echo off

FOR /F "tokens=1-3 delims= " %%G in ('type pcplus.txt ^|findstr /C:"PAYMENT TOTAL:" /C:"CHECK/EFT NUMBER:"') DO (
IF "%%G"=="PAYMENT" >>log.txt set/p"=%%G %%H %%I "<nul
IF "%%G"=="CHECK/EFT" >>log.txt echo %%G %%H %%I
)
Output from the file you supplied.

Code: [Select]

PAYMENT TOTAL: 21115.36 CHECK/EFT NUMBER: 0000011111111
PAYMENT TOTAL: 11189.01 CHECK/EFT NUMBER: 0000011111111
PAYMENT TOTAL: 16.18 CHECK/EFT NUMBER: 0000011111111
PAYMENT TOTAL: 1188.00 CHECK/EFT NUMBER: 0000011111111
PAYMENT TOTAL: 12130.17 CHECK/EFT NUMBER: 0000011111111
PAYMENT TOTAL: 2102.22 CHECK/EFT NUMBER: 0000011111111


[daillest319]

great!   thanks squashman. if possible can you explain what is occuring on these two lines after the >>log.txt for example whats removing the the data after the payment amount and how doesthe script know where the payment amount ends and to remove anything after it? also if possbile can the file be output like so...

Code: [Select]

PAYMENT TOTAL:  CHECK/EFT NUMBER:
21115.36 0000011111111
11189.01 0000011111111
16.18 0000011111111
1188.00 0000011111111
12130.17 0000011111111
2102.22 0000011111111


I Have the bottom portion using the below code im just trying to get the first row to show as above

Code: [Select]

FOR /F "tokens=1-3 delims= " %%G in ('type pcplus.txt ^|findstr /C:"PAYMENT TOTAL:" /C:"CHECK/EFT NUMBER:"') DO (

IF "%%G"=="PAYMENT" >>log.txt set/p"=%%I "<nul
IF "%%G"=="CHECK/EFT" >>log.txt echo %%I

)





[Squashman]

If you know that is what you want as the first line of your output file then hard code it before the for loop.  K.I.S.S.

[daillest319]

thank again for the tip .  Sorry but i realize im going to need a few more things from the file.
i'm trying to see how i can also capture
PAYMENT DATE:
RUN DATE:
also need the the ones i listed below but im note sure how i can go about capturing them since the names  are always changing. But there always in the same position in the file
EMPIR
PAYERS CC
and also need CC separately. CC

Code: [Select]

@echo off
Echo PAYMENT TOTAL: CHECK/EFT NUMBER: PAYMENT DATE: RUN DATE: PAYERS: PAYER: >>log.txt
FOR /F "tokens=1-3 delims= " %%G in ('type pcplus.txt ^|findstr /C:"PAYMENT DATE:" /C:"RUN DATE:" /C:"PAYMENT TOTAL:" /C:"CHECK/EFT NUMBER:"') DO (

IF "%%G"=="PAYMENT" >>log.txt set/p"=%%G %%H %%I "<nul
IF "%%G"=="CHECK/EFT" >>log.txt echo %%G %%H %%I

REM I tried the below but it does not work
IF "%%G"=="PAYMENT DATE:" >>log.txt echo %%G %%H %%I
IF "%%G"=="RUN DATE:" >>log.txt echo %%G %%H %%I
)


[Squashman]

thank again for the tip .  Sorry but i realize im going to need a few more things from the file.

That has been a common theme on most of the forums i belong to lately.

Please explain in more detail what you all need. Please show me an example of a few transactions of input and what you want the output to look like.

[daillest319]

attached is a new sample. the output need to look like below but does not need all the spaces i placed bewteen each field. i just did that so it would be eaiser to read.

i need
PAYMENT TOTAL:
CHECK/EFT NUMBER:
PAYMENT DATE:
RUN DATE:   
PAYERS:      located underneath payment date
PAYER:   located in the smae line as the GPO BOX
PAY: located underneath payment date the last two letters after the payer name
ex. EMPIRS CC  = CC

Code: [Select]

PAYMENT TOTAL: CHECK/EFT NUMBER: PAYMENT DATE: RUN DATE:    PAYERS:      PAYER:   PAY:
21115.36   0000011331111   06/19/13    06/20/13     EMPIRS CC    EMPIR     CC
11189.01   0000011221111   06/19/13    06/20/13     HPPPPP HP    HPPPP     HP
16.18     0000011341111    06/18/13    06/20/13     APPPPA AP    APPPP      AP
1188.00    0000011441111    06/19/13    06/20/13     EMPIRS CC    EMPIR     CC
12130.17   0000011541111   06/17/13    06/20/13     EMPIRS CC    EMPIR     CC
2102.22    0000016411111   06/16/13    06/20/13     APPPPA AP    APPPP     AP


[recovering disk space, attachment deleted by admin]

[foxidrive]

EDITED: This works with your sample text:

Code: [Select]

@echo off
>"newfile.txt" echo PAYMENT TOTAL: CHECK/EFT NUMBER: PAYMENT DATE: RUN DATE: PAYERS: PAYER: PAY:
setlocal enabledelayedexpansion
for /f "tokens=1,2,3,4,12" %%a in (pcplus.txt) do (
 if defined check_eft_number (
   >>"newfile.txt" echo !payment_total! !check_eft_number! !payment_date! !run_date! !payers! !payer! !pay!
   set payment_total=
   set check_eft_number=
   set payment_date=
   set run_date=
   set payers=
   set payer=
   set pay=
   )
 if "%%b"=="DATE:" set payment_date=%%c& set run_date=%%e
 if "%%c"=="ELECTRONIC" set pay=%%b& set payers=%%a %%b
 if "%%a"=="GPO" set payer=%%d
 if "%%b"=="TOTAL:" set payment_total=%%c
 if "%%b"=="NUMBER:" set check_eft_number=%%c

)



PAYMENT TOTAL: CHECK/EFT NUMBER: PAYMENT DATE: RUN DATE: PAYERS: PAYER: PAY:
21115.36 0000011331111 06/19/13 06/20/13 EMPIRS CC EMPIR CC
11189.01 0000011221111 06/19/13 06/20/13 HPPPPP HP HPPPP HP
16.18 0000011341111 06/18/13 06/20/13 APPPPA AP APPPP AP
1188.00 0000011441111 06/19/13 06/20/13 EMPIRS CC EMPIR CC
12130.17 0000011541111 06/17/13 06/20/13 EMPIRS CC EMPIR CC
2102.22 0000016411111 06/16/13 06/20/13 APPPPP AP APPPP AP

[daillest319]

this works perfect foxidrive thank you so much. btw If you have any free time i would like to understand what occuring in the script if you can write notes to what each line is doing i would really appericate it im just curious how it know where to grab certain string for example the string in the GPO line. thank you all again i really do appericate all the help i recieve in this forum.

[foxidrive]

When you parse a file in a forINdo loop the default delimiter is tab and space.  So for each line the spaces and tabs are removed and the remaining 'words' are attached to tokens.

You will see that I specified tokens 1 to 4 and 12.  If you count the words in each line, which are each associated with %%a %%b %%c %%d %%e you can see that the if conditions are checking for a specific word on a specific spot on the line, and if that word is found then it sets a variable to the desired word.

When the last item in the set is found check_eft_number it echos all the stored variables on the next loop, and then resets them all to prepare for the next record.

So like very many batch files, it is tailored to the format of the text file.

[Squashman]

this works perfect foxidrive thank you so much. btw If you have any free time i would like to understand what occuring in the script if you can write notes to what each line is doing i would really appericate it im just curious how it know where to grab certain string for example the string in the GPO line. thank you all again i really do appericate all the help i recieve in this forum.

If you turn ECHO ON and watch each line execute it will also help you understand what is going on.
And remember in the future to explain everything you need upfront otherwise the code has a tendency to change drastically when you change the parameters.  That wastes everyone's time and remember the help is free around here.