Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Create table

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Create table
# 8  
Old 05-05-2010
New version:
Code:
awk -F'|' '
NR==1 {
   for ( c=1; c<=NF; c++ ) {
      colName[c] = $c;
   }
   colCount = NF;
   next;
}
{
   for ( c=1; c<=NF; c++ ) {
           if ( $c ~ /^[0-9]+$/        )          colIsNum[c]++
      else if ( $c ~ /^[0-9]*\.[0-9]*$/ )         colIsDec[c]++
      else if ( $c ~ /^[0-9]+\/[0-9]+\/[0-9]+$/ ) colIsDat[c]++
      else                                        colIsTxt[c]++;
      if (length($c) > colLength[c]) colLength[c] = length($c);
   }
}
END {
   print "create table tablename("
   for ( c=1; c<=colCount; c++ ) {
      out = "  " colName[c];
           if ( colIsTxt[c] ) out = out " varchar2(" colLength[c] ")"
      else if ( colIsDat[c] ) out = out " date"
      else if ( colIsDec[c] ) out = out " number(38,4)"
      else if ( colIsNum[c] ) out = out " number(" colLength[c] ")"
      else                    out = out " Error!";
      if ( c != colCount ) out = out ",";
      print out
   }
   print ");";
}
' inputfile

Inputfile :
Code:
col1|col2|col3|col4|col5
1234|zxxxx|999|300.2|01/01/1970
12|abcdefghij|xyz|1024|10/08/2010

Output :
Code:
create table tablename(
  col1 number(4),
  col2 varchar2(10),
  col3 varchar2(3),
  col4 number(38,4),
  col5 date
);

Jean-Pierre.
# 9  
Old 05-06-2010
Hi Aigles,
Thanks for your reply .Its working for all columns except last column.
For the last column,set data type with column name .
problem with 4th column,its start with decimal places but the o/p showing varchar2(13)

Input file
Code:
col1|col2|col3|col4|col5|col6|col7|col8
1234|zxxxx|999|   |01/01/1970|||xxxx
12|abcdefghij|xyz|13.0000000000|10/08/2010||commentsxx|REAL xxxx- xxxx WILL ONLY BE xxxx; renegotiated 12/16/2000, yyy 47106812 ;payment xxx

Output
Code:
create table tablename(
  col1 number(4),
  col2 varchar2(10),
  col3 varchar2(3),
  varchar2(13),
  col5 date,
  col6 varchar2(2),
  col7 varchar2(10),
  varchar2(67) col8
);

Regards,
MR
Last edited by mohan705; 05-06-2010 at 12:19 AM..
# 10  
Old 05-06-2010
Quote:
Originally Posted by mohan705
Hi Aigles,
Thanks for your reply .Its working for all columns except last column.
For the last column,set data type with column name .
problem with 4th column,its start with decimal places but the o/p showing varchar2(13)

Input file
Code:
col1|col2|col3|col4|col5|col6|col7|col8
1234|zxxxx|999|   |01/01/1970|||xxxx
12|abcdefghij|xyz|13.0000000000|10/08/2010||commentsxx|REAL xxxx- xxxx WILL ONLY BE xxxx; renegotiated 12/16/2000, yyy 47106812 ;payment xxx

Output
Code:
create table tablename(
  col1 number(4),
  col2 varchar2(10),
  col3 varchar2(3),
  varchar2(13),
  col5 date,
  col6 varchar2(2),
  col7 varchar2(10),
  varchar2(67) col8
);

Regards,
MR
The 4th Column in row #2 doesn't contain a number (only spaces), so the column is assigned the varchar2 type.

Jean-Pierre.

---------- Post updated at 08:27 ---------- Previous update was at 08:18 ----------

With your last sample datas, i don't have the problem on the last column name :
Input file:
Code:
col1|col2|col3|col4|col5|col6|col7|col8
1234|zxxxx|999|   |01/01/1970|||xxxx
12|abcdefghij|xyz|13.0000000000|10/08/2010||commentsxx|REAL xxxx- xxxx WILL ONLY BE xxxx; renegotiated 12/16/2000, yyy 47106812 ;p
ment xxx

Output:
Code:
create table tablename(
  col1 number(4),
  col2 varchar2(10),
  col3 varchar2(3),
  col4 varchar2(13),
  col5 date,
  col6 varchar2(),
  col7 varchar2(10),
  col8 varchar2(85)
);

but there is a problem with the 6th column which is empty for all rows.

Have you modified the posted script ?
Our outputs are not the same.

Jean-Pierre.
# 11  
Old 05-06-2010
Hi Aigles,
There is no space in the 4 thc column ,but it still showing string data type.Plese advise

input file
Code:
col1|col2|col3|col4|col5|col6|col7|col8
1234|zxxxx|999|32.3000|01/01/1970|||xxxx
12|abcdefghij|xyz|13.0000000000|10/08/2010||commentsxx|REAL xxxx- xxxx WILL ONLY BE xxxx; renegotiated 12/16/2000, yyy 47106812 ;payment xxx

output
Code:
create table tablename(
  col1 number(4),
  col2 varchar2(10),
  col3 varchar2(3),
  col4 varchar2(13),
  col5 date,
  col6 varchar2(),
  col7 varchar2(10),
  col8 varchar2(85)
);

Regards,
MR
# 12  
Old 05-06-2010
I ran the script with your datas, and the result seems fine for me :
Code:
$ cat mohan.sh
awk -F'|' '
NR==1 {
   for ( c=1; c<=NF; c++ ) {
      colName[c] = $c;
   }
   colCount = NF;
   next;
}
{
   for ( c=1; c<=NF; c++ ) {
           if ( $c ~ /^[0-9]+$/        )          colIsNum[c]++
      else if ( $c ~ /^[0-9]*\.[0-9]*$/ )         colIsDec[c]++
      else if ( $c ~ /^[0-9]+\/[0-9]+\/[0-9]+$/ ) colIsDat[c]++
      else                                        colIsTxt[c]++;
      if (length($c) > colLength[c]) colLength[c] = length($c);
   }
}
END {
   print "create table tablename("
   for ( c=1; c<=colCount; c++ ) {
      out = "  " colName[c];
           if ( colIsTxt[c] ) out = out " varchar2(" colLength[c] ")"
      else if ( colIsDat[c] ) out = out " date"
      else if ( colIsDec[c] ) out = out " number(38,4)"
      else if ( colIsNum[c] ) out = out " number(" colLength[c] ")"
      else                    out = out " Error!";
      if ( c != colCount ) out = out ",";
      print out
   }
   print ");";
}
' mohan.txt
$ cat mohan.txt
col1|col2|col3|col4|col5|col6|col7|col8
1234|zxxxx|999|32.3000|01/01/1970|||xxxx
12|abcdefghij|xyz|13.0000000000|10/08/2010||commentsxx|REAL xxxx- xxxx WILL ONLY BE xxxx; renegotiated 12/16/2000, yyy 47106812 ;payment xxx
$ ./mohan.sh
create table tablename(
  col1 number(4),
  col2 varchar2(10),
  col3 varchar2(3),
  col4 number(38,4),
  col5 date,
  col6 varchar2(),
  col7 varchar2(10),
  col8 varchar2(85)
);
$

Jean-Pierre.
# 13  
Old 05-06-2010
Hi Aigless,

Thanks for your great help .Want to check with you ,if the columns contains spaces or null and numbers ,will not assign number data type .The i/p file some of the columns are null and some with date/number values ,its assigned to varchar2 data type.Is it any otherway to check to ignore null or spaces and assign correspoing data type.


Thanks in advance
MR
# 14  
Old 05-06-2010
The following new version of the AWK program ignore null and spaces.
Code:
awk -F'|' '
BEGIN {
   colDefaultLength = 1;
}
NR==1 {
   for ( c=1; c<=NF; c++ ) {
      colName[c] = $c;
   }
   colCount = NF;
   next;
}
{
   for ( c=1; c<=NF; c++ ) {
      if (length($c) > colLength[c]) colLength[c] = length($c);
           if ( $c ~ /^ *$/ )                     continue;
           if ( $c ~ /^[0-9]+$/        )          colIsNum[c]++
      else if ( $c ~ /^[0-9]*\.[0-9]*$/ )         colIsDec[c]++
      else if ( $c ~ /^[0-9]+\/[0-9]+\/[0-9]+$/ ) colIsDat[c]++
      else                                        colIsTxt[c]++;
   }
}
END {
   print "create table tablename("
   for ( c=1; c<=colCount; c++ ) {
      if ( colLength[c]+0 == 0 ) colLength[c] = colDefaultLength;
      out = "  " colName[c];
           if ( colIsTxt[c] ) out = out " varchar2(" colLength[c] ")"
      else if ( colIsDat[c] ) out = out " date"
      else if ( colIsDec[c] ) out = out " number(38,4)"
      else if ( colIsNum[c] ) out = out " number(" colLength[c] ")"
      else                    out = out " varchar2(" colLength[c] ")";
      if ( c != colCount ) out = out ",";
      print out
   }
   print ");";
}
' inputfile

Inputfile :
Code:
col1|col2|col3|col4|col5|col6|col7|col8
1234|zxxxx|999|32.3000|01/01/1970|||xxxx
12|abcdefghij|xyz|13.0000000000|10/08/2010||commentsxx|REAL xxxx- xxxx WILL ONLY BE xxxx
|not empty|foo|   |05/05/2010||text|

Output:
Code:
create table tablename(
  col1 number(4),
  col2 varchar2(10),
  col3 varchar2(3),
  col4 number(38,4),
  col5 date,
  col6 varchar2(1),
  col7 varchar2(10),
  col8 varchar2(33)
);

Jean-Pierre.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Create table within awk-if statement

Hi I am trying to create a table within an awk if statement awk -F, '{ if ($8 ~ /Match/) BEGIN{print "<table>"} {print "<tr>";for(i=1;i<=NF;i++)print "<td>" $i"</td>";print "</tr>"} END{print "</table>"}' SN1.csv | mailx -s "Your details" abc@123.com But this doesnt work.. Please suggest (8 Replies)
Discussion started by: sidnow
8 Replies

2. Shell Programming and Scripting

Create table using tbl command

Hi, I required the output like attached: I tried using the below command and i'm getting error: file with data: .TS box; cB s s c|c|c PO Download statistics - Host to SOM Time;Receive Time;Processing Time;PO count 4.30 AM OMHPO File;Tue Feb 21 04:39:55 EST 2012;Tue Feb... (3 Replies)
Discussion started by: cns1710
3 Replies

3. Shell Programming and Scripting

Create Script from table

I have a table ABC with 4 columns and below data Col1,Col2,Col3,Col4 prod,10,12,joba prod,10,11,jobb qa,10,12,jobc I want to create an output file like this Server:prod StartTime:10 EndTime:12 JobName:joba Server:prod StartTime:10 EndTime:11 JobName:jobb (3 Replies)
Discussion started by: traininfa
3 Replies

4. Shell Programming and Scripting

Create Pivot table

I would like to use awk to parse a file with three columns in, like: Chennai,01,1 Chennai,07,1 Chennai,08,3 Chennai,09,6 Chennai,10,12 Chennai,11,19 Chennai,12,10 Chennai,13,12 Kerala,09,2 AP,10,1 AP,11,1 Delhi,13,1 Kerala,13,3 Chennai,00,3 Chennai,01,1 Chennai,02,1 Chennai,07,5 (3 Replies)
Discussion started by: boston_nilesh
3 Replies

5. UNIX and Linux Applications

create table via stored procedure (passing the table name to it)

hi there, I am trying to create a stored procedure that i can pass the table name to and it will create a table with that name. but for some reason it creates with what i have defined as the variable name . In the case of the example below it creates a table called 'tname' for example ... (6 Replies)
Discussion started by: rethink
6 Replies

6. UNIX for Dummies Questions & Answers

Create a table - very new to unix

I need to create a simple table of information by grepping several columns from various files and display them all at once with simple headers on top. Can anyone help get me started? I am very new to unix so I really have no idea how to work with this and I appreciate any help I can get! Let me... (11 Replies)
Discussion started by: aj250
11 Replies

7. Shell Programming and Scripting

to create an output file as a table

Hi, I have four input files and would like to create an output file as a table. Please check the example below. File 1. 111111 222222 333333 444444 File 2. 555555 666666 777777 888888 File 3. aaaaa bbbbb ccccc ddddd (2 Replies)
Discussion started by: marcelus
2 Replies

8. Shell Programming and Scripting

[ORACLE] CREATE TABLE in bash

Hey, I want to create a table in bash, my db server is oracle. You can find my script below: I am very confused. I tried to run this script many times without any luck. Please help! (6 Replies)
Discussion started by: radek
6 Replies

9. BSD

How to create IP table at Free BSD

Now, I had installed free bsd at my office. Unfortunitely, Email server have been using Local PoP3 and SMTP to our ISP with outlook. but my unix firewall sever ( free bsd ) didn't allow these port ( 110 & 25 ). How can i create the IP table to pass at server. If u have any experience about obvious... (4 Replies)
Discussion started by: Ashraff Ali
4 Replies

10. Shell Programming and Scripting

create new table/field

Dear Folks, If I have 2 files, say A and B in format: A: $1 $2 01032 12856 01041 13351 01042 11071 01042 12854 01042 12862 01042 12866 . . . and B: (2 Replies)
Discussion started by: Gr4wk
2 Replies
Login or Register to Ask a Question