?
Solved

Retrieve value from text block

Posted on 2003-03-26
8
Medium Priority
?
1,123 Views
Last Modified: 2013-12-26
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
Comment
Question by:simsam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 48

Expert Comment

by:Tintin
ID: 8214153
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
 
LVL 5

Accepted Solution

by:
ecw earned 2000 total points
ID: 8214242
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
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8214739
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8214774
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
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8214800
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
 
LVL 3

Expert Comment

by:prady_21
ID: 8215603
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
 

Author Comment

by:simsam
ID: 8217567
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
 
LVL 5

Expert Comment

by:ecw
ID: 8219378
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here is how to use MFC's automatic Radio Button handling in your dialog boxes and forms.  Beginner programmers usually start with a OnClick handler for each radio button and that's just not the right way to go.  MFC has a very cool system for handli…
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question