0

I want to insert data (only rows having extended keyword) present in .txt file into Oracle database in the format ID,Data,Date,Project Name where ID, date and project name are present in environment variables.

File.txt has below data:

Writing main object(name=abc)
writing Extended object (name=%abc(123&rest,type=pqr)

logdata.txt should have below data:

A1234C,(name=%abc(123&rest,type=pqr),12022018_11:12:20,DEV:Sales Project

While copying the data, special characters like %,( etc present in the file.txt are missing in the output file logdata.txt.

Please find below code :

set file=D:\MSTR_CICD\file.txt
for /F "usebackq tokens=2*delims=(" %%a in (`findstr  "extended" "%file%"`) do (
    set /A i+=1
    call set array[%%i%%]=%%a
    call set n=%%i%%
)

for /L %%i in (1,1,%n%) do call echo %User_ID%,%%array[%%i]%%,%Timestamp%,%proj%  >> D:\MSTR_CICD\Batch_Script\logdata.txt

Please correct the code or let me know how can i achieve this. Also, my input file can have any special character as it contain logs of an application.

Mofi
  • 46,139
  • 17
  • 80
  • 143
Akansha Chopra
  • 11
  • 4
  • 5
    You seem to be way over complicating the task. Why are you not writing out the data to the log file in the first `FOR` command? – Squashman Oct 04 '18 at 14:59

1 Answers1

0

This batch file can be used for this task:

@echo off
setlocal EnableExtensions DisableDelayedExpansion
set "proj=DEV:Sales Project"
set "User_ID=A1234C"
set "Timestamp=12022018_11:12:20"
set "InputFile=D:\MSTR_CICD\file.txt"
set "DataFile=D:\MSTR_CICD\Batch_Script\logdata.txt"

if exist "%InputFile%" (
    for /F delims^=^ eol^= %%I in ('%SystemRoot%\System32\findstr.exe /I /C:Extended "%InputFile%"') do (
        set "DataLine=%%I"
        setlocal EnableDelayedExpansion
        set "DataLine=!DataLine:*(=(!"
        set "DataLine=!DataLine:"=""!"
        echo %User_ID%,"!DataLine!",%Timestamp%,%proj%
        endlocal
    )
) >"%DataFile%"

if exist "%DataFile%" for %%I in ("%DataFile%") do if %%~zI == 0 del "%DataFile%"

:EndBatch
endlocal

FINDSTR runs in the separate command process started by FOR in background with cmd.exe /C a case-insensitive, literal search for the string Extended on the input file and outputs all lines containing this string to handle STDOUT.

FOR captures this output and processes them line by line. FOR ignores empty lines and by default also lines starting with a semicolon because of ; is the default end of line character. And FOR splits up the line into substrings (tokens) using space/tab as delimiter and assigns just the first substring to specified loop variable by default.

By using the FOR option string delims^=^ eol^= an empty list of delimiters and and no end of line character is set to disable line splitting and ignoring lines starting with a semicolon. As this special option string cannot be enclosed in double quotes, it is necessary to escape the space and the two equal signs with caret character to get those three characters outside a double quoted argument string interpreted as literal characters and not as argument string separators.

The entire line as output by FINDSTR found in file is assigned to environment variable DataLine. This is done with delayed environment variable expansion disabled to process also lines correct containing one or more exclamation marks. Otherwise cmd.exe would double parse the line set "DataLine=%%I" after having replaced %%I by the current line and would interpret every ! in the line as begin/end of an environment variable reference resulting in unwanted modification of the line before assigning it to the environment variable.

The usage of command CALL on a line with command SET results also in double parsing the command line before executing the command SET which is the reason why some characters are missing in the environment variables array produced by your code.

For details see also How does the Windows Command Interpreter (CMD.EXE) parse scripts?

After having assigned the line to the environment variable, it is necessary to enable delayed expansion to further process the data line in the FOR loop. That makes the batch file slow, but can't be avoided in this case. Read this answer for details about the commands SETLOCAL and ENDLOCAL.

The first modification on the data line is removing everything left to first (.

The second modification on the data line is replacing all " by "" in the line to escape every double quote according to CSV specification.

Then the remaining data line is output together with the other data enclosed in double quotes as the data line can contain also one or more commas which requires according to CSV specification that the data is enclosed in double quotes.

For CSV specification read for example the Wikipedia article about comma-separated values.

Everything output by ECHO inside FOR loop is redirected to the specified data file which overwrites a by chance already existing data file with same name.

It is possible that FINDSTR does not find any line containing Extended in any case resulting in producing a data file with 0 bytes. The empty data file is deleted by the second FOR.

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

  • del /?
  • echo /?
  • endlocal /?
  • findstr /?
  • for /?
  • goto /?
  • if /?
  • set /?
  • setlocal /?
Mofi
  • 46,139
  • 17
  • 80
  • 143