Shell Programming and Scripting

file.txt

col1|col2|col3|col4|col5
1234|zxxxx|xcvvv|300|null

file.sh

file=$1
c1=`head -1 $file|tr "|" "\n" |cat -n`
echo "$c1"
cnt=`echo "$c1"|nawk -F " " '{print $1}'`
#echo "$cnt"

for i in `echo $c1`
do
echo $i
done

using the below ,will find out max length
 awk -F "|" '{ if (length($83) > max) max = length($83) } END { print max }'    file


cat filename|egrep '[0-9]' ---for numbers

car filename |egrep '[a-z][A-Z]' --for strings

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]+$/) colIsText[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 (colIsText[c]) 
         out = out " varchar2(" colLength[c] ")";
      else
         out = out " number(" colLength[c] ",2)";
      if (c != colCount) out = out ",";
      print out
   }
   print ");";
}
' inputfile

col1|col2|col3|col4|col5
1234|zxxxx|xcvvv|300|null
12|abcdefghij|xyz|1024|comment

create table tablename(
  col1 number(4,2),
  col2 varchar2(10),
  col3 varchar2(5),
  col4 number(4,2),
  col5 varchar2(7)
);

ex:

col1|col2|col3|col4|col5
1234|zxxxx|xcvvv|300.200|10/08/2008 

create table tablename(
  col1 number(4),
  col2 varchar2(10),
  col3 varchar2(5),
  col4 number(38,4),
  col5 date
);