Shell Programming and Scripting

Hi experts.

I got a file (500mb max) and need to pivot it (loading into ORCL) and change BLANK delimiter to PIPE |.
Sometimes there are multipel BLANKS (as a particular value may be BLANK, or simply two BLANKS instead of one BLANK).

thanks for your input!

Cheers,


Layout
<id>BLANK<record_type>BLANK<field_num>BLANK<field_value>BLANK<field_num> BLANK<field_value>BLANK ........\n

example: (005, 004 are record_type, one leading for each line, same with <id>)

1234 005 001 23 198 5 098 45 033 2.........090 12\n
1234 006 008 66 002 345 129 2 345 2 897 45 091 56788 123 56321 443 45.........100 33\n

I want to use awk to "massage" this file to this output:
<record_type> <field_num> <field_value>
<record_type> <field_num> <field_value>

Example:
1234|005|001|23\n
1234|005|198|5\n
1234|005|098|45\n
1234|005|033|2\n
...
1234|005|090|12\n
1234|006|008|66\n
1234|006|002|345\n
1234|006|129|2\n
1234|006|345|2\n
1234|006|897|45\n
1234|006|091|56788\n
1234|006|443|45\n
....
1234|006|100|33\n

Try...

Code:

awk 'BEGIN{FS="\\ ";OFS="|"} {for(i=3;i<=NF;i+=2) print $1,$2,$i,$(i+1)}' file1 > file2

Hi Ygor,

thanks for the input. We used your suggestion and altered it to this version, which works fine... despite that it breaks when 100 is reached.
Any idea?


while [ $n -lt 98 ]

do
awk '{if ($'$n'!= "") print "'$var1'", "'$var2'","'$var3'", "'$var4'","'$var5'","'$var 6'", $1 ,$"'$n'",$"'$m'" }' T.$1 >> B.$1

n=`expr $n + 2`
m=`expr $n + 1`
done

You must be using an old version of awk. Try gawk or nawk instead.
Not sure why you have moved the loop outside of awk, which is very inefficient.
Also, you can pass variables to awk on the command line like this...

Code:

awk '{for(n=3;n<=NF;n+=2) if($n) print v1,v2,v3,$1,$n,$(n+1)}' v1=$var1 v2=$var2 v3=$var3 file1 > file2