OK I think this one can only be solved by a pro. (Extract fields and table from a .sql file

OK I think this one can only be solved by a pro. (Extract fields and table from a .sql file

Post by Dafel » Sat, 03 May 2003 03:18:44



Anyone have a stript or advise on how to extract file name, table
names and field names from 13 stored procedures

For example

Procedure1
Select
aaa
,bbb
,ccc
,ddd
,eee
from
table1, table2

Procedure2
Select
aaa
,bbb
,ccc
,ddd
,eee
from
table1, table2, table3

I'd like to create a flatfile formated as followed

Procedure1|aaa|bbb|ccc|ddd|eee|*|table1|table2
Procedure2|aaa|bbb|ccc|ddd|eee|*|table1|table2|table3

Thanks in advance

 
 
 

OK I think this one can only be solved by a pro. (Extract fields and table from a .sql file

Post by Barry Margoli » Sat, 03 May 2003 03:39:00




>Anyone have a stript or advise on how to extract file name, table
>names and field names from 13 stored procedures

>For example

>Procedure1
>Select
>aaa
>,bbb
>,ccc
>,ddd
>,eee
>from
>table1, table2

>Procedure2
>Select
>aaa
>,bbb
>,ccc
>,ddd
>,eee
>from
>table1, table2, table3

>I'd like to create a flatfile formated as followed

>Procedure1|aaa|bbb|ccc|ddd|eee|*|table1|table2
>Procedure2|aaa|bbb|ccc|ddd|eee|*|table1|table2|table3

Untested solution:

awk '/^$/ { }
     !inproc && /^Procedure/ { printf("%s|", $0); inproc=1 }
     !selecting && /^Select/ { selecting=1 }
     selecting && /^from/ { selecting=0 }
     { if (selecting) {
         sub("^,", ""); /* remove the leading comma if necessary */
         printf("%s|", $0);
       } else {
         gsub(", *", "|");
         printf("*|%s\n", $0)
         inproc=0;
       }
     }

--

Genuity Managed Services, a Level(3) Company, Woburn, MA
*** DON'T SEND TECHNICAL QUESTIONS DIRECTLY TO ME, post them to newsgroups.
Please DON'T copy followups to me -- I'll assume it wasn't posted to the group.

 
 
 

OK I think this one can only be solved by a pro. (Extract fields and table from a .sql file

Post by Stephane CHAZELA » Sat, 03 May 2003 04:40:38


[...]

Quote:> Procedure1
> Select
> aaa
> ,bbb                >
> ,ccc                >
> ,ddd                > I'd like to create a flatfile formated as followed
> ,eee                >
> from                > Procedure1|aaa|bbb|ccc|ddd|eee|*|table1|table2
> table1, table2      > Procedure2|aaa|bbb|ccc|ddd|eee|*|table1|table2|table3

> Procedure2          > Thanks in advance
> Select
> aaa
> ,bbb
> ,ccc
> ,ddd
> ,eee
> from
> table1, table2, table3

gawk '{$(NF-1)="*";$2="";gsub(/[,|]+ */,"|")}1' RS= FS=\\n OFS=\|

--
Stphane

 
 
 

OK I think this one can only be solved by a pro. (Extract fields and table from a .sql file

Post by John W. Krah » Sat, 03 May 2003 06:12:05



> Anyone have a stript or advise on how to extract file name, table
> names and field names from 13 stored procedures

> For example

> Procedure1
> Select
> aaa
> ,bbb
> ,ccc
> ,ddd
> ,eee
> from
> table1, table2

> Procedure2
> Select
> aaa
> ,bbb
> ,ccc
> ,ddd
> ,eee
> from
> table1, table2, table3

> I'd like to create a flatfile formated as followed

> Procedure1|aaa|bbb|ccc|ddd|eee|*|table1|table2
> Procedure2|aaa|bbb|ccc|ddd|eee|*|table1|table2|table3

perl -00l012pe's/from/,*,/i;s/\s*(select|,)\s*/|/ig' yourfile

John
--
use Perl;
program
fulfillment

 
 
 

OK I think this one can only be solved by a pro. (Extract fields and table from a .sql file

Post by rakesh shar » Sat, 03 May 2003 10:28:14



> Anyone have a stript or advise on how to extract file name, table
> names and field names from 13 stored procedures

> For example

> Procedure1
> Select
> aaa
> ,bbb
> ,ccc
> ,ddd
> ,eee
> from
> table1, table2

> Procedure2
> Select
> aaa
> ,bbb
> ,ccc
> ,ddd
> ,eee
> from
> table1, table2, table3

> I'd like to create a flatfile formated as followed

> Procedure1|aaa|bbb|ccc|ddd|eee|*|table1|table2
> Procedure2|aaa|bbb|ccc|ddd|eee|*|table1|table2|table3

Here's the perl solution:

#!/usr/local/bin/perl -w
$/="";




        $y[-2] = "*";


        print "\n";

Quote:}

Here's the sed solution:

#!/bin/sed -f
/^Procedure/!d
:loop
$!{
        N
        /\nfrom$/!bloop
        N

Quote:}

s/\nSelect\n/|/
s/\nfrom\n/|*|/
s/\n,/|/g
s/\n/|/g
s/,  */|/g

############################################################

 
 
 

1. How do I extract a field between two other fields?

given a file like so:
 VG Name                /dev/vg03
   PV Name                /dev/dsk/c0d0s2
VG Name                /dev/vg01
   PV Name                /dev/dsk/c1d0s2
VG Name                /dev/vg00
   PV Name                /dev/dsk/c2d0s2
VG Name                /dev/vg02
   PV Name                /dev/dsk/c3d0s2
VG Name                /dev/vg07
   PV Name                /dev/dsk/c6d0s2
VG Name                /dev/vg05
   PV Name                /dev/dsk/c7d0s2
VG Name                /dev/vg06
   PV Name                /dev/dsk/c8d0s2
VG Name                /dev/vg04
   PV Name                /dev/dsk/c9d0s2

I need to extract the PV field(s) between the VG Name field(not including
the VG Name field) as there could be more than one PV Name field per volume
group.

I am not sure of how to do this, I am assuming that grep,awk, or sed is
involved Would anyone be as so kind as to provide me with a snippet of code
that may produce the output that I want?
Any help would be greatly appreciated!!

2. Goodstuff

3. sed: extracting single field from each line of a CSV file

4. getting taper to preserve access times on files backed-up?

5. extracting fields by keyword from large text file, using bash

6. NONE

7. faster way to extract fields from large file?

8. Need Makefile help (.m4->.y->y.tab.[ch])

9. Compiling whit gcc, I got an error : I think a library is not ok, which one ?

10. Extracting names from the symbol table of an executable file?

11. Extracting names from symbol table of an executable file?

12. Help: Extracting the symbol table from ELF object files?

13. tar question: how do I extract some files without extracting directories