sed error

Shell Programming and Scripting
1

Hi ,
Can some body help me why CREATE TABLE is not printing in my out put for the below code?

I tried the same but still out put is not accurate.

sed -n '
  1d
  s/^\([^ ]\{1,33\} \)\(.*\)/CREATE TABLE "("\1(\2","/
  :loop
  ${
    s/,$/ );/
    p
    }
  N
  s/\n\(.*\)/ \1,/
  b loop
 ' schema_file.txt

out put:

TAB1 COL1 INTEGER COL2 INTEGER, COL3 INTEGER, COL4 INTEGER, );
2

please explain more.

3

A txt file is having the columns like this
TABLENAME COLUMNS DATATYPE
TAB1 COL1 INTEGER
COL2 INTEGER
COL3 INTEGER
COL4 INTEGER

printf should be

CREATE TABLE TAB1 (COL INTEGER,COL2,INTEGER,COL3,INTEGER,COL4 INTEGER);

when i tried the above sed the CREATE TABLE is not printing...

Regards

4

For input file

use following command

to generate following output

5
6

Hello panyam ,
Thanks for your help its working for me.

Is there any possiblity to extend this code like ...if we have input file like.

TAB1 COL1 DATATYP1 NULL
COL2 DATA TYP2 NOT NULL
COL3 DAT TYPE3 NOT NULL
..
..
COL N
out put should be

CREATE TABLE (COL1 DATATYPE1 NULL,COL2 DATATYPE2 NOT NULL,COL3 DATATYPE3 ....COL N DATA TYPEN NOT NULL);

because i have a raw Excel sheet which has columns like the above format using that Excel sheet i need to prepare a CREATE TABLE statemetns for all entries of Excel.so i am copying manually each table contenet in a txt file so that using this AWK command i can prepare a DDL for all tables at one shot.

Regards

7

For your current input,

this should work fine:

8

Hi Pranyam,

any idea why the below sed is not working for me Just CREATE TABLE statement is missing.

sed -n '
  1d
  s/^\([^ ]\{1,33\} \)\(.*\)/CREATE TABLE "("\1(\2","/
  :loop
  ${
    s/,$/ );/
    p
    }
  N
  s/\n\(.*\)/ \1,/
  b loop
 ' schema_file.txt

out put is coming like this ,

TAB1 COL1 INTEGER COL2 INTEGER, COL3 INTEGER, COL4 INTEGER,
9

hi,

may i make small change?

expected output:

CREATE TABLE (COL1 DATATYPE1 NULL,COL2 DATATYPE2 NOT NULL,COL3 DATATYPE3 ....COL N DATA TYPEN NOT NULL);

s="CREATE TABLE (" t " "$0
instead of 
s="CREATE TABLE " t " ("$0
10
11

But

"CREATE TABLE ( col1 type1 , .....) " is a wrong syntax

Proper syntax is :

12

Hi dhiren,
Can i know where is the input file name in this syntax?

$ cat temp.awk
BEGIN{ }
{

if(NR>1)
{
        if(NF==3 && NR==2)
        {
                printf("CREATE TABLE %s(%s %s",$1, $2, $3)
        }

        if(NF==3 && NR>2)
        {
                printf(")\nCREATE TABLE %s(%s %s",$1,$2,$3)
        }

        if(NF==2)
        {
                printf(",%s %s", $1, $2)
        }
}

}
END{
        printf(")\n")
}
13

Hi Rocking77

temp.awk is input AWK script .

for example your input file name is file.txt then you can run program as follow

14

[LEFT]TAB1 COL1 INTEGER NOT NULL
COL2 CHAR NOT NULL WITH DEFAULT
COL3... INTEGER NOT NULL WITH DEFAULT

                   COLn     INTEGER   NOT NULL  WITH DEFAULT

Hi all Thanks for your help.Now i have 5 columns in my input file (this is last trail to my scripting i am about to achieve my out put please bear me).

For this input i need out put like .

[RIGHT]CREATE TABLE TAB1(COL1 INTEGER NOT NULL,
COL2 CHAR NOT NULL WITH DEFAULT,
COL3... INTEGER NOT NULL WITH DEFAULT,

                   COLn     INTEGER   NOT NULL  WITH DEFAULT\)

IN TABSPACE INDEX IN INDEXSPACE;

[/RIGHT]
[/LEFT]

15

Still,

My awk statement should work.

You only need to add "IN TABSPACE INDEX IN INDEXSPACE; " in the print statement in the END.

Cheers
Ravi

16

When i tried your awk,
Input given was

COL1 INTEGER NOTNULL
COL2 INTEGER NOTNUL
COL INTEGER NOTNULL
out put is coming like

CREATE TABLE COL2(INTEGER NOT NULL)
CREATE TABLE COL3(INTGER NOT NULL)
...

like this can you please format little more .

17

It mean , you did not test it properly.

How ever , below is the code as per your requirement:

awk '{if(NR==1)
{
 t=$1;
$1=""
s="CREATE TABLE " t " ("$0
next;
}
s=s" ,"$0
}
END { 
i="IN TABSPACE INDEX IN INDEXSPACE;"
print s" ) "i }' input_file