Cutting numbers

[nirs33]

Hi guys,

I have the following sql loader log file:
....
Table TOVED593:
5758 Rows successfully loaded.
0 Rows not loaded due to data errors.
3233282 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Table TOVED596:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
3239040 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Table TOVED597:
1642 Rows successfully loaded.
0 Rows not loaded due to data errors.
3237398 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
.....

I need to cut the table_name,the numer of rows which successfully loaded and the number of rows which not loaded due to data errors,into a new file,as the following example:

TOVED593 5758 0
TOVED596 0 0
TOVED597 1642 0

How should i suppose to do it?

Thanks in advance,
Nir

 

[bigoldbulldog]

#! /usr/bin/sed -nf

/Table .*:/ {
s/.* //
s/://
N
s/\n \([0-9]*\).*/ \1/
N
s/\n \([0-9]*\).*/ \1/
p
}

save and run (e.g. <cmd> *log ).

Cheers,
ND

bigoldbulldog@hotmail.com

 

[CaKiwi]

Or use the following awk program

/^Table/ {
  sub(/:/,"",$2)
  printf $2 " "
  getline
  printf $1 " "
  getline
  print $1
}

CaKiwi

&quot;I love mankind, it's people I can't stand&quot; - Linus Van Pelt

 

[nirs33]

Thanks a lot ND and CaKiwi!!
I'll check it tomorrow and i'll let you know.

Nir

 

[nirs33]

Hi ND,

I've run your program on my log file,and i've got a good results but in addition i've got &quot;garbage&quot;:

./nir.csh toved.log > a

vi a

....
'222')
Insert option in effect for this table: TRUNCATE

'229')
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
'255')
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
'256')
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
'260')
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
'274')
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
'278')
Insert option in effect for this table: TRUNCATE
....
'589')
Insert option in effect for this table: TRUNCATE

'593')
Insert option in effect for this table: TRUNCATE

'596')
Insert option in effect for this table: TRUNCATE

'597')
Insert option in effect for this table: TRUNCATE

TOVED200 46487 3
TOVED201 36572 0
TOVED202 44775 0
TOVED203 42642 0
TOVED205 42897 0
TOVED207 38281 0
TOVED209_TEMP 206927 0
TOVED508 93962 7
TOVED215 130579 0
TOVED218 42944 0
TOVED219_TEMP 65733 229
TOVED220 14565 0
TOVED222 8163 0
TOVED229 34709 0
TOVED255 29324 34
TOVED256 17182 0
TOVED260_TEMP 116112 7
TOVED274 59337 0
TOVED278 46489 1
.....

Only the last period of &quot;a&quot; is good:
....
TOVED255 29324 34
TOVED256 17182 0
TOVED260_TEMP 116112 7
....

How can we get rid of the &quot;garbage&quot;?

Thanks in advance,
Nir

 

[bigoldbulldog]

Hmmm,

Seems like the earlier part of the your log is being grabbed. The first or second line of the example is being pulled-in and is causing your problems. Perhaps you have a colon in the table name. I don't recall this being possible or maybe it's your version of sql loader or even your version of sed.

Table OPS$FOO.BAR, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PATNO FIRST * | CHARACTER
VISIT NEXT * | CHARACTER
DCI NEXT * | CHARACTER
NYHACD NEXT * | CHARACTER


Table OPS$FOO.BAR:
5881 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.



I'd say tightening up the regular expression will fix it up. An extra cleaning step will do this too but be kludgy. An example of your log will definately get this solved in a robust way.

Cheers,
ND

bigoldbulldog@hotmail.com