How to read and manipulate file? 1

zanzemaj · Jun 14, 2007

I have a textfile named tmp.txt which contains the following record:

Code:

06/13/2007 23:05:23  ARCHIVING DATABASE "table1"
06/13/2007 23:05:28  "table1" - LOCK RELEASED
06/13/2007 23:05:28
06/13/2007 23:05:28  ARCHIVING DATABASE "arctab3"
06/13/2007 23:05:30  TABLE "ARCHIVE_DB" - 438 BYTES, 0 ROWS ARCHIVED FOR THIS
                     STREAM
06/13/2007 23:05:30  TABLE "tab3" - 438 BYTES, 0 ROWS ARCHIVED FOR
                     THIS STREAM
06/13/2007 23:05:30  TABLE "tab2" - 438 BYTES, 0 ROWS ARCHIVED
                     FOR THIS STREAM
06/13/2007 23:05:30  TABLE "table8" - 438 BYTES, 0 ROWS ARCHIVED
                     FOR THIS STREAM
06/13/2007 23:05:35  ARCHIVING DATABASE "DBA_INFO"
06/13/2007 23:05:37  TABLE "Accountinfo" -  57,757 BYTES, 725 ROWS ARCHIVED FOR
                     THIS STREAM
06/13/2007 23:05:37  TABLE "AccountInfo_V2R4" -  53,312 BYTES, 644 ROWS
                     ARCHIVED FOR THIS STREAM
06/13/2007 23:05:41  TABLE "AllRights" -  44,077,114 BYTES, 477,103 ROWS
                     ARCHIVED FOR THIS STREAM
06/13/2007 23:05:41  TABLE "canary" -  80,438 BYTES,   5,000 ROWS ARCHIVED FOR
                     THIS STREAM
06/13/2007 23:05:41  TABLE "Child_Allrights" -  27,727 BYTES, 201 ROWS ARCHIVED
                     FOR THIS STREAM
06/13/2007 23:05:42  TABLE "column_exclude_list" - 512 BYTES, 3 ROWS ARCHIVED
                     FOR THIS STREAM
06/13/2007 23:05:42  TABLE "dba_userid" -  11,938 BYTES, 94 ROWS ARCHIVED
                     FOR THIS STREAM
06/13/2007 23:05:42  TABLE "dba_useridx" -   7,018 BYTES, 53 ROWS ARCHIVED
                     FOR THIS STREAM
06/13/2007 23:05:42  VIEW  "LOBJTBL" - ARCHIVED
06/13/2007 23:05:42  VIEW  "LOGTBL" - ARCHIVED
06/13/2007 23:05:42  VIEW  "sqltbl" - ARCHIVED
06/13/2007 23:05:42  VIEW  "summarytbl" - ARCHIVED
06/13/2007 23:05:42  TABLE "DB_DEF_ACCOUNT" -  41,353 BYTES, 439 ROWS
                     ARCHIVED FOR THIS STREAM
06/13/2007 23:05:42  TABLE "useridtab" - 658 BYTES, 0 ROWS ARCHIVED FOR
                     THIS STREAM
06/13/2007 23:05:42  MACRO "GENERATE_VIEW" - ARCHIVED
06/13/2007 23:05:42  VIEW  "INDICES" - ARCHIVED
06/13/2007 23:05:43  TABLE "Parent_Allrights" -   7,072 BYTES, 48 ROWS ARCHIVED
                     FOR THIS STREAM
06/13/2007 23:05:43  MACRO "macro1" - ARCHIVED
06/13/2007 23:05:43  MACRO "MACRONODE2" - ARCHIVED

Please help me to have another file named out.txt with this format based on tmp.txt:

Code:

ARCHIVING DATABASE "table1" 
ARCHIVING DATABASE "arctab3" TABLE "ARCHIVE_DB" - 438 BYTES, 0 ROWS
ARCHIVING DATABASE "arctab3" TABLE "tab3" - 438 BYTES, 0 ROWS
ARCHIVING DATABASE "arctab3" TABLE "tab2" - 438 BYTES, 0 ROWS 
ARCHIVING DATABASE "arctab3"  TABLE "table8" - 438 BYTES, 0 ROWS 
ARCHIVING DATABASE "DBA_INFO" TABLE "Accountinfo" -  57,757 BYTES, 725 ROWS 
ARCHIVING DATABASE "DBA_INFO" TABLE "AccountInfo_V2R4" -  53,312 BYTES, 644 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "AllRights" -  44,077,114 BYTES, 477,103 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "canary" -  80,438 BYTES,   5,000 ROWS 
ARCHIVING DATABASE "DBA_INFO" TABLE "Child_Allrights" -  27,727 BYTES, 201 ROWS 
ARCHIVING DATABASE "DBA_INFO" TABLE "column_exclude_list" - 512 BYTES, 3 ROWS 
ARCHIVING DATABASE "DBA_INFO" TABLE "dba_userid" -  11,938 BYTES, 94 ROWS 
ARCHIVING DATABASE "DBA_INFO" TABLE "dba_useridx" -   7,018 BYTES, 53 ROWS 
ARCHIVING DATABASE "DBA_INFO" VIEW  "LOBJTBL" 
ARCHIVING DATABASE "DBA_INFO" VIEW  "LOGTBL" 
ARCHIVING DATABASE "DBA_INFO" VIEW  "sqltbl" 
ARCHIVING DATABASE "DBA_INFO" VIEW  "summarytbl" 
ARCHIVING DATABASE "DBA_INFO" TABLE "DB_DEF_ACCOUNT" -  41,353 BYTES, 439 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "useridtab" - 658 BYTES, 0 ROWS 
ARCHIVING DATABASE "DBA_INFO" MACRO "GENERATE_VIEW"
ARCHIVING DATABASE "DBA_INFO" VIEW  "INDICES" 
ARCHIVING DATABASE "DBA_INFO" TABLE "Parent_Allrights" -   7,072 BYTES, 48 ROWS 
ARCHIVING DATABASE "DBA_INFO" MACRO "macro1" 
ARCHIVING DATABASE "DBA_INFO" MACRO "MACRONODE2"

I need to get ARCHIVING DATABASE, the word that follows that and then continue with information when I see the word TABLE, VIEW or MACRO. Here's what I have started, please feel free to suggest any solution. But I cannot get the other lines.

Code:

awk '/ARCHIVING DATABASE/{x=$0;getline;print x" "$0}' tmp.txt | tr '"' -d | tr '-' -d | tr ',' -d | cut -d" " -f6-15 | sed 's!../../.... ..:..:..!!;s!BYTES!!;s!ROWS.*$!!;s!  *! !g'   > out.txt

aigles · Jun 14, 2007

You can do something like that :

Code:

#!/usr/bin/awk -f
# Awk program : fmt.awk

/ARCHIVING DATABASE/ {
   db_prefix = "ARCHIVING DATABASE " $5;
   print db_prefix;
   next;
}

$3 ~ /TABLE/ {
   out = db_prefix;
   for (f=3; f<= NF && f<=9; f++)
      out = out " " $f;
   print out;
}

$3 ~ /VIEW|MACRO/ {
   out = db_prefix;
   for (f=3; f<= NF && f<=4; f++)
      out = out " " $f;
   print out;
}

Output:

Code:

$ [COLOR=blue]awk -f fmt.awk tmp.txt[/color]
ARCHIVING DATABASE "table1"
ARCHIVING DATABASE "arctab3"
ARCHIVING DATABASE "arctab3" TABLE "ARCHIVE_DB" - 438 BYTES, 0 ROWS
ARCHIVING DATABASE "arctab3" TABLE "tab3" - 438 BYTES, 0 ROWS
ARCHIVING DATABASE "arctab3" TABLE "tab2" - 438 BYTES, 0 ROWS
ARCHIVING DATABASE "arctab3" TABLE "table8" - 438 BYTES, 0 ROWS
ARCHIVING DATABASE "DBA_INFO"
ARCHIVING DATABASE "DBA_INFO" TABLE "Accountinfo" - 57,757 BYTES, 725 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "AccountInfo_V2R4" - 53,312 BYTES, 644 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "AllRights" - 44,077,114 BYTES, 477,103 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "canary" - 80,438 BYTES, 5,000 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "Child_Allrights" - 27,727 BYTES, 201 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "column_exclude_list" - 512 BYTES, 3 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "dba_userid" - 11,938 BYTES, 94 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "dba_useridx" - 7,018 BYTES, 53 ROWS
ARCHIVING DATABASE "DBA_INFO" VIEW "LOBJTBL"
ARCHIVING DATABASE "DBA_INFO" VIEW "LOGTBL"
ARCHIVING DATABASE "DBA_INFO" VIEW "sqltbl"
ARCHIVING DATABASE "DBA_INFO" VIEW "summarytbl"
ARCHIVING DATABASE "DBA_INFO" TABLE "DB_DEF_ACCOUNT" - 41,353 BYTES, 439 ROWS
ARCHIVING DATABASE "DBA_INFO" TABLE "useridtab" - 658 BYTES, 0 ROWS
ARCHIVING DATABASE "DBA_INFO" MACRO "GENERATE_VIEW"
ARCHIVING DATABASE "DBA_INFO" VIEW "INDICES"
ARCHIVING DATABASE "DBA_INFO" TABLE "Parent_Allrights" - 7,072 BYTES, 48 ROWS
ARCHIVING DATABASE "DBA_INFO" MACRO "macro1"
ARCHIVING DATABASE "DBA_INFO" MACRO "MACRONODE2"
$

Jean-Pierre.

zanzemaj · Jun 14, 2007

Thank you Jean-Pierre for the quick reply. However, I need to put the code inside a KSH script, this below do not work. I am really new with awk and would like to ask your assistance how to read this tmp.txt file.

Code:

#!/usr/bin/ksh
awk '/ARCHIVING DATABASE/ {
   db_prefix = "ARCHIVING DATABASE " $5;
   print db_prefix;
   next;
}

$3 ~ /TABLE/ {
   out = db_prefix;
   for (f=3; f<= NF && f<=9; f++)
      out = out " " $f;
   print out;
}

$3 ~ /VIEW|MACRO/ {
   out = db_prefix;
   for (f=3; f<= NF && f<=4; f++)
      out = out " " $f;
   print out;
}'

Thank you.

aigles · Jun 15, 2007

You can include the awk program in a ksh script in the following ways :

1) Create the awk program file fmt.awk and include the following statement in your KSH script file :

Code:

 awk -f fmt.awk tmp.txt

2) If you don't want to use an external file, you can include all the awk program in your KSH script file :

Code:

awk '
   /ARCHIVING DATABASE/ {
      db_prefix = "ARCHIVING DATABASE " $5;
      print db_prefix;
      next;
   }

   $3 ~ /TABLE/ {
      out = db_prefix;
      for (f=3; f<= NF && f<=9; f++)
         out = out " " $f;
      print out;
   }

   $3 ~ /VIEW|MACRO/ {
      out = db_prefix;
      for (f=3; f<= NF && f<=4; f++)
         out = out " " $f;
      print out;
   }
    ' tmp.txt

Jean-Pierre.

zanzemaj · Jun 15, 2007

Thank you for your response. I modified it a little to output to a new file, I used the code inside the KSH script. It works as expected.

Code:

awk '
   /ARCHIVING DATABASE/ {
      db_prefix = "ARCHIVING DATABASE " $5;
      print db_prefix;
      next;
   }

   $3 ~ /TABLE/ {
      out = db_prefix;
      for (f=3; f<= NF && f<=9; f++)
         out = out " " $f;
      print out;
   }

   $3 ~ /VIEW|MACRO/ {
      out = db_prefix;
      for (f=3; f<= NF && f<=4; f++)
         out = out " " $f;
      print out;
   }
    ' tmp.txt > out.txt

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to read and manipulate file? 1

zanzemaj

Programmer

aigles

Technical User

zanzemaj

Programmer

aigles

Technical User

zanzemaj

Programmer

Similar threads

Part and Inventory Search

Sponsor