Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

Retrieve value from text block

I am attempting to read a log file from a database import to determine the rows that failed in order to write to an exception file.  An example block from the log file is as follows:

   SQL3124W  The field value in row "6" and column "2" cannot be converted to a
   PACKED DECIMAL value, but the target column is not nullable.  The row was not
   loaded.

   SQL3123W  The field value in row "7" and column "3" cannot be converted to a
   PACKED DECIMAL value.  A null was loaded.

The only consistent part I can search on is the text "The row was not loaded".

I need to treat the 3 lines together as a single block to do my search to pull my row number.

I have been using AWK to this point, but would be interested in any solution.

Thanks.
0
simsam
Asked:
simsam
1 Solution
 
TintinCommented:
If you have GNU grep, you can specify number of lines before/after the match or lines of context.

grep -C 3 "The row was not loaded"
0
 
ecwCommented:
if the record seperator (RS) is an empty string, awk treats the blank lines as record seperators, eg.

awk 'BEGIN {RS = "" } {print NR, $1, $0}'<<EOF
SQL3124W  The field value in row "6" and column "2" cannot be converted to a PACKED DECIMAL value, but the target column is not nullable.  The row was not loaded.

SQL3123W  The field value in row "7" and column "3" cannot be converted to a PACKED DECIMAL value.  A null was loaded.
EOF

will output,
1 SQL3124W SQL3124W  The field value in row "6" and column "2" cannot be converted to a PACKED DECIMAL value, but the target column is not nullable.  The row was not loaded.
2 SQL3123W SQL3123W  The field value in row "7" and column "3" cannot be converted to a PACKED DECIMAL value.  A null was loaded.

0
 
HamdyHassanCommented:
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HamdyHassanCommented:
If this is IBM : Software : Data Management : DataJoiner :

then we are talking about range of Messages SQL3100W to SQL30301W
http://www-3.ibm.com/software/data/datajoiner/booksv2/djxp4m34.htm

I means you can know exactly which messages you are care about from this list, then we can search them

something like "SQL31[23|24|...etc"

0
 
HamdyHassanCommented:
Here is perl script, input file is log.txt and output will be log.out
the script will search for "The row was not loaded" then print out this line and two lines before it

let me know if this is what you need


#!/usr/bin/perl

#----------------------
# Define input file
#----------------------
$sourceFile="log.txt";

#---------------------------------------------------
# Define output files
#---------------------------------------------------
$DATAFile="log.out";

#----------------------
# Create File Handlers
#----------------------
open(IN,"$sourceFile");
open(OUTA,">$DATAFile");

$l1="";
$l2="";

while ($myline=<IN>){
  if ($myline =~ /The row was not loaded/)
  {
    print OUTA $l2 ;
    print OUTA $l1 ;
    print OUTA $myline ;
  }
  $l2=$l1;
  $l1=$myline;
}
close(IN);
close(OUTA);
0
 
prady_21Commented:
This program in perl exactly satisfies what you want
:)
if there is anything do ask me

#!/usr/bin/perl

$sourcefile="xxxxx";
$output="xxxxx";
open ( FILE , "$sourcefile");
open ( OUTFILE, ">$output");

 while ( $line = <FILE>) {
    while ($line !~ /^$/ ) {
       chomp($line);
       $string .= $line;
       $line = <FILE>;
    }
    if ( $string =~ /The\s*row\s*was\s*not\s*loaded/g ) {
        print OUTFILE "$string\n";
    }
    $string = "";
 }
close FILE;
close OUTFILE;
exit 0;



0
 
simsamAuthor Commented:
This was exactly what I was looking for.  For anyone interested, here is what my syntax ended up looking like to get all the row numbers I needed.

awk 'BEGIN {RS=""} {FS="\""}
/not \n*loaded/ {print $2}
/SQL3185W/ {print $2}' $INPUT_FILE > $work_file

For the sample I gave, this returned 6 for the one row that was not loaded.

Thanks again.

0
 
ecwCommented:
simsam,

Here,
  awk 'BEGIN {RS=""} {FS="\""}
  ... etc ...

You're setting FS="\"" for every input record.  why not set it just once in the BEGIN action, ie.
  awk 'BEGIN {RS=""; FS="\""}

It's slightly more efficient.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now