unix extract data from file

Question

I've got a file.sql file from an informix database export. The following is a small part of the file (I changed the data a little bit to make it anonymous):

grant dba to "xxx";
grant dba to "yyy";
grant dba to "zzz";

{ TABLE "xxx".table1 row size = 66 number of columns = 5 index size = 54 }
{ unload file name = table00100.unl number of rows = 307 }

create table "xxx".table1 
  (
    field1 char(2) not null ,
    field2 char(10) not null ,
    field3 char(30),
    field4 char(20) not null ,
    field5 date not null 
  );
revoke all on "xxx".table1 from "yyy";

What I need from this file is to name the table00100.unl file back to the original table name. So I need an output like this:

mv table00100.unl table1

I've managed to to this with 2 files in between with a little of awk and sed, but isn't this possible in an easier way without the temporary files in between? My code sample:

awk '{for(i=1;i<=NF;i++) {if ($i=="unload") {print $(i+4)} else {if ($i=="TABLE") print $(i+1)}}}' file.sql | sed 's/".*".//' > temp.out
awk 'NR%2{printf "%s ",$0;next;}1' temp.out | awk '{for (i=NF;i>0;i--) if (i > 1) printf("mv %s ",$i); else printf("%s\n",$i)}' > temp.shl

georgexsh · Accepted Answer · 2018-09-27 07:11:17Z

4

if you want to use awk solely:

/TABLE/ {
    sub("\".+\"\\.", "", $3);
    table = $3;
}
/unload/ {
    print "mv", $6, table;
};

answered Sep 27, 2018 at 7:11

georgexsh

16.8k3 gold badges45 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nightfox79 Over a year ago

Can I change the print "mv", $6, table; to system("mv " $6 " " table); to execute the move immediately?

georgexsh Over a year ago

of course, you could, but IMHO, you'd better output those scripts to console or a file to examine its correctness first.

Ed Morton Over a year ago

The first arg for sub() is a regexp not a string so use regexp delimiters so awk doesn't have to parse it twice, i.e. sub(/".+"\./, "", $3). Also the move should provide quotes, printf "mv \047%s\047 \047%s\047\n", $6, table so when the shell executes it later it's not exposed for globbing, word splitting and filename expansion. Finally - you don't need the trailing semicolons on each line.

Ed Morton Over a year ago

@nightfox79 you really shouldn't have awk involved in printing mv or executing mv at all as that's creating tight coupling between the awk script and the calling shell script. Just have awk find and print the from and to names and then let the calling shell shell do the mv or whatever else with them.

steffen · Accepted Answer · 2018-09-27 07:52:32Z

1

Similar to georgexsh's solution, but with gensub:

awk '/TABLE/{table=gensub(/.*\./, "", "", $3)}/unload/{print "mv", $6, table }'

answered Sep 27, 2018 at 7:52

steffen

17.4k4 gold badges47 silver badges87 bronze badges

Comments

kvantour · Accepted Answer · 2018-09-27 07:04:36Z

0

awk '($0 ~ /{ *TABLE/)  { match($0,/TABLE */); to=substr($0,RSTART+RLENGTH);
                          match(to," ");       to=substr(to,1,RSTART-1);
                          match(to,"[.]");     to=substr(to,RSTART+1);
                        }
     ($0 ~ /{ *unload/) { match($0,"name *= *"); from=substr($0,RSTART+RLENGTH); 
                          match(from," ");       from=substr(from,1,RSTART-1)
                        }
     (from!="") && (to!="") { exit }
     END {print "mv "from" "to}' file

The reason I make it so "complicated" is that I am not sure about all the spacings in your input if they will be consistent and if the ordering in the braces will always be the same.

edited Sep 27, 2018 at 7:04

answered Sep 27, 2018 at 6:58

kvantour

26.9k4 gold badges57 silver badges81 bronze badges

1 Comment

nightfox79 Over a year ago

This gives only the first table, I have over 300 lines like that, how can I loop this

stack0114106 · Accepted Answer · 2018-09-27 12:18:14Z

Using a perl one line ( of course it is long :-))

> cat informix_unload.txt
grant dba to "xxx";
grant dba to "yyy";
grant dba to "zzz";

{ TABLE "xxx".table1 row size = 66 number of columns = 5 index size = 54 }
{ unload file name = table00100.unl number of rows = 307 }

create table "xxx".table1
  (
    field1 char(2) not null ,
    field2 char(10) not null ,
    field3 char(30),
    field4 char(20) not null ,
    field5 date not null
  );
revoke all on "xxx".table1 from "yyy";

grant dba to "xxx";
grant dba to "yyy";
grant dba to "zzz";

{ TABLE "xxx".table2 row size = 66 number of columns = 5 index size = 54 }
{ unload file name = table00200.unl number of rows = 307 }

create table "xxx".table2
  (
    field1 char(2) not null ,
    field2 char(10) not null ,
    field3 char(30),
    field4 char(20) not null ,
    field5 date not null
  );
revoke all on "xxx".table1 from "yyy";
-- other data

> perl -ne 'BEGIN{$x=qx(cat informix_unload.txt);while($x=~m/(.+?)unload file name = (\S+)(.+?)create table (\S+)(.+)/osm){$x=$5;print "$2 $4\n";}exit}'
table00100.unl "xxx".table1
table00200.unl "xxx".table2
>

check this out

Collectives™ on Stack Overflow

unix extract data from file

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related