How to extract certain text from a text file and replace keyword in another file with this text?

Question

Long story short I have an SQL script that I run when an action falls over, I have a log with has all the information I need and I just replace certain parts of this SQL script with text from the log file.

My question: is there a way I can do this from a batch file?

Example

Log file contains query ID:123456 and EntityID:654321, 23123432, 345345435, 435435, 438980943, etc.

I have SQL script where I replace "QueryID" with the query id number from the log and also I want to replace "entityID" with all of the entityid's that are in the log file there are usually a few hundred.

Here's the SQL script

select * 
from search_results 
where search_id = ***query_id*** 
  and entity_id in (select entity_id 
                    from search_results 
                    where search_id = ***query_id***
                      and entity_id in (***entityID***))

Above is the SQL script I run and I want to replace Query_id with the query id from the log file I have and then replace the Entity_ID with the entity ID's from the same log file

I've been trying to find a way round this for ages but I'm stuck I use the follow to extract the entity ID's to a file and then I just copy and paste them in to the SQL script

@ECHO OFF
for /f "tokens=4 delims=:" %%G in ('TYPE logfile.txt ^|FIND /i "EntityID"') do (
    ECHO %%G,>>entity.txt
)

I can do the same for the query Id but as there is just the one id I just copy & paste it in.

In short I want to place a log in a directory/file and then run a Bat file to generate me the SQL script (in a text file).

Any help will be much appreciated :)

Extra information

I have to run this script to figure out what people in an eshot, the eshot has/hasn't been sent to so I can removed those people that have had the eshot sent to them and remove them from the list in SQL so the eshot can be set again and people don't get lots of emails.

The QueryID is the id of the list where the emails are listed and the entityID is the id of the email addresses in the list.

So in short the sql script finds the list of email address (via the QueryID) and then shows the email address the eshot has been sent to (via entityID's in the log file) so I can remove them from the list and resend the eshot and people don't get two emails.

This usually falls over when people have stupid characters in their email addresses.

Here's the log file I've removed data sensitive information.

>  ==========================================================================================
>     
>     ------------------------------------------------------------------------------------------
>     Domain      :10007
>     User        :10725
>     Language    :1
>     Entity      :0
>     Role        :-1
>     View        :8997749 ()
>     Region      :8998836 ()
>     Control     :
>     Date/Time   :5 janvier 2015 15:49:33 UTC
>     ==========================================================================================
>     [15:49:33 UTC] TotalQueryIDs :1:
>     [15:49:33 UTC] User :10725:
>     [15:49:33 UTC] Template :20005406:
> !!!    [15:49:33 UTC] queryID :96566:
>     [15:49:33 UTC] QueryCount :781:
>     [15:49:33 UTC] Count :1:
> !!!    [15:49:33 UTC] EntityID :49891:
>     [15:49:33 UTC] Contact :49891:
>     [15:49:33 UTC] Today :05/01/15:
>     [15:49:33 UTC] Time :16:49:
>     [15:49:33 UTC] EmailID ::
>     [15:49:33 UTC] posOfSign :7:
>     [15:49:33 UTC] posOfSign :7:
>     [15:49:33 UTC] posOfDot :10:
>     [15:49:33 UTC] emailValidity ::
>     [15:49:33 UTC] flag ::
>     [15:49:33 UTC] Language :English:
>     [15:49:33 UTC] Template1 :COBE8:
>     [15:49:33 UTC] flag ::
>     [15:49:33 UTC] --<*> RSH <*>--
>     [15:49:33 UTC] TempName ::
>     [15:49:33 UTC] DocCount :96:
>     [15:49:33 UTC] BeforeAdd ::
>     [15:49:33 UTC] AfterAdd ::
>     [15:49:33 UTC] afterIF ::
>     [15:49:33 UTC] preview :N:
>     [15:49:33 UTC] preview :N:
>     [15:49:34 UTC] DocCount :1:
>     [15:49:34 UTC] countofATT ::
>     [15:49:34 UTC] Today :05/01/15:
>     [15:49:34 UTC] Time :16:49:
>     [15:49:34 UTC] EMAILSEND ::
>     [15:49:34 UTC] preview :N:
>     [15:49:34 UTC] Role :111:
>     [15:49:34 UTC] e1 ::>     
>     [15:49:34 UTC] e1 ::
>     [15:49:34 UTC] DocCount :96:
>     [15:49:34 UTC] DocCount :96:
>     [15:49:34 UTC] e1 ::
>     [15:49:34 UTC] countofATT ::
>     [15:49:34 UTC] DocCount :1:
>     [15:49:34 UTC] e1 ::
>     [15:49:34 UTC] --<*> About to Save Journal <*>-->     
>     [15:49:34 UTC] --<*> Journal Saved <*>--
>     [15:49:34 UTC] EmailID ::
>     [15:49:34 UTC] Count :2:
> !!!    [15:49:34 UTC] EntityID :49903:
>     [15:49:34 UTC] Contact :49903:
>     [15:49:34 UTC] Today :05/01/15:
>     [15:49:34 UTC] Time :16:49:
>     [15:49:34 UTC] EmailID ::
>     [15:49:34 UTC] posOfSign :3:
>     [15:49:34 UTC] Eshot :Y:
>     [15:49:34 UTC] posOfSign :3:
>     [15:49:34 UTC] posOfDot :11:
>     [15:49:34 UTC] emailValidity ::
>     [15:49:34 UTC] flag ::
>     [15:49:34 UTC] Phone ::
>     [15:49:34 UTC] Language :English:
>     [15:49:34 UTC] Template1 :COBE8:
>     [15:49:34 UTC] flag ::

Here is an example of what the SQL script should look like

    select * from search_results where search_id =96566 and
entity_id in (select entity_id from search_results where search_id =96566
And entity_id in(49891,49903,51404,137395,137492,))

Thank you for all you help guys, really appreciate it.

---

Update

I've use the following (Thanks to @Keeghan McGarry) and get what I want but the entity id only shows the last one in the list, is there a way to get all the entity ID's to show up, FYI The log is called bo.txt and the script goes to sql.txt

@ECHO OFF  
FOR /F "tokens=4 delims=:" %%e IN ('TYPE bo.txt ^|FIND /i "EntityID"') DO SET entity=%%e
FOR /F "tokens=4 delims=:" %%q IN ('TYPE bo.txt ^|FIND /i "queryID"') DO SET query=%%q

ECHO select * from search_results where search_id =%query% and entity_id in >> sql.txt  
ECHO (select entity_id from search_results where search_id =%query%>> sql.txt  
ECHO And entity_id in>> sql.txt  
ECHO (%entity%))>> sql.txt

The above gives me this below but there should be these entity id's 49891,49903,51404,137395,137492,

select * from search_results where search_id =96566 and entity_id in (select entity_id from search_results where search_id =96566 And entity_id in (137492))

All help is really appreciated.

Answer 1

My recommendation would be to set a variable for your EntityID and QueryID instead of outputting to a separate file. That way you can just echo to a file the entire SQL Script with variables in the places they need to be. A very brief example(Untested as I dont have your LogFile.txt):

@ECHO OFF  
FOR /F "tokens=4 delims=:" %%F IN ('TYPE logfile.txt ^|FIND /i "EntityID"') DO SET SET entity=%%F  
FOR /F "***Need to set these***" %%G IN ('TYPE logfile.txt ^|FIND /i "query_ID"') DO SET SET query=%%G  
ECHO select * from search_results where search_id = %query% and entity_id in > query.txt  
ECHO (select entity_id from search_results where search_id = %query% >> query.txt  
ECHO And entity_id in >> query.txt  
ECHO (%entity%)) >> query.txt 

All you need is to put the correct tokens and delims for the query ID and it should then work. If you could provide a sample LogFile.txt then I could test/expand this code.

hope this helps

EDIT

As per the edited question, to get every entityID place the query output inside the FOR loop.

@ECHO OFF  
setlocal enabledelayedexpansion

FOR /F "tokens=4 delims=:" %%q IN ('TYPE bo.txt ^|FIND /i "queryID"') DO SET query=%%q
FOR /F "tokens=4 delims=:" %%e IN ('TYPE bo.txt ^|FIND /i "EntityID"') DO (
    SET entity=%%e
    ECHO select * from search_results where search_id =%query% and entity_id in >> sql.txt  
    ECHO (select entity_id from search_results where search_id =%query%>> sql.txt  
    ECHO And entity_id in>> sql.txt 
    ECHO (!entity!^)^)>> sql.txt
    ECHO.>> sql.txt
)

As you can see I switched around the order of the FOR loops, with queryID now going first and the outputs to sql.txt are inside the other loop so it will output it every time it finds an entityID. The added ECHO.>> sql.txt is so that each query in sql.txt is broken up.

Answer 2

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
SET "destdir=U:\destdir"
SET "queryid="
FOR /f "tokens=4,5 delims=[]:" %%a IN (
  'type q27849431.txt^|findstr /i /l /c:" queryID " /c:" EntityID "'
 ) DO (
 IF "%%a"==" queryID " CALL :generate %%b
 IF "%%a"==" EntityID " SET "entityids=!entityids!,%%b"
  )
)
CALL :generate 0

GOTO :EOF

:generate
IF NOT DEFINED queryid GOTO first
SET "entityids=%entityids: =%"
(
 ECHO select * from search_results where search_id = %queryid% and entity_id in 
 ECHO (select entity_id from search_results where search_id = %queryid%
 ECHO And entity_id in 
 ECHO (%entityids:~1%^)^)^)
)>"%destdir%\query%queryid%.sql"

:first
SET /a queryid=%1
SET "entityids="
GOTO :eof

Well - some data provided. I believe it's extraordinarily unlikely that the log lines actually start >Spaces and the target lines contain the !!!, so I removed those from the data and placed the result in a file named q27849431.txt for my testing.

I also assumed that there is one logfile to be processed (q27849431.txt) and that its structure is

[15:49:33 UTC] TotalQueryIDs :1:
[15:49:33 UTC] queryID :96577:
[15:49:33 UTC] EntityID :49891:
[15:49:34 UTC] EntityID :49903:
[15:49:33 UTC] TotalQueryIDs :1:
[15:49:33 UTC] queryID :96588:
[15:49:33 UTC] EntityID :49896:
[15:49:34 UTC] EntityID :49943:
[15:49:34 UTC] posOfSign :3:

(showing significant and random lines - especially lines containing the significant string queryid regardless of case)

From there, the batch is VB-programmer's play. Find the queryid then build a string of entityids. Output a new query each time queryid changes, one for luck at the end of logfile (as there will be no further queryids but one remains accumulated but not output)

Each sql is produced to its own file with an obvious name. If you want them all to the same file, then change >"%destdir%\query%queryid%.sql" to >>"whateverdestinationfilenameyouwant" but that construct will append to any existing "whateverdestinationfilenameyouwant" so it may be an idea to delete "whateverdestinationfilenameyouwant" at the start of the script.

Answer 3

With everyone's help, especially @magoo I've finally got an answer :)

@ECHO OFF

setlocal enabledelayedexpansion

FOR /F "tokens=4 delims=:" %%q IN ('TYPE bo.txt ^|FIND /i "queryID"') DO (SET query=%%q)

    ECHO select * from search_results where search_id =%query% and entity_id in >> sql.txt  
    ECHO (select entity_id from search_results where search_id =%query%>> sql.txt  
    ECHO And entity_id in(>> sql.txt 

FOR /F "tokens=4 delims=:" %%e IN ('TYPE bo.txt ^|FIND /i "EntityID"') DO (SET entity=%%e
    ECHO !entity!>> sql.txt)
echo )) >>sql.txt

The above script generates the following SQL script:

select * from search_results where search_id =96566 and entity_id in   
(select entity_id from search_results where search_id =96566  
And entity_id in( 
49891
49903
51404
137395
137492
)) 

From the log file in the question.

I will added in a line to find and delete sql.txt if it already exists before running the rest of the script so not to have lots of SQL scripts in the same file.

Thanks for all you help Guys