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.
simsamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HamdyHassanCommented:
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.