Hi.
I am reasonably new to awk, but have done quite a lot of unix scripting in the past. I have resolved the issues below with unix scripting but it runs like a dog. Moved to awk for speed and functionality but running up a big learning curve in a hurry, so hope there is some help here.

I am doing some data manipulation using awk and am having trouble with a couple of functions. I thought I would try here for some advice.

The data file that I am working with is from a Pick database, so has fields with comma delimiters, then within a field there can be more (subvariable) data separated by square bracket delimiters (]).

The data (for this example) is basically a quantity / price data file.
An example is.

DEVICES,DESCRIPTION OF DEVICES,24]99]499]999]2999]99999,22.35]21.60]19.85]18.35]17.62]16.00
BAGS,DESCRIPTION OF BAGS,24]99]499]999]4999]99999,
ITEMS,DESCRIPTION OF CARS,24]99]499]999]2999]99999,]]]]17.62]0.00
BAGS,DESCRIPTION OF BAGS,24]99]499]999]4999]99999,12.30]]


I am trying to achieve a flat file with all fields in it, comma separated.

So far I can split out the fields correctly

awk 'BEGIN { FS="," ; OFS="," ;} \
{
split($3,QTY,"]")
split($4,PR,"]")
} \

{ if (PR[1]=="")
{PR[1]=0 }
}\
{ if (PR[2]=="")
{PR[2]=0 }
}\
{ if (PR[3]=="")
{PR[3]=0 }
}\
{ if (PR[4]=="")
{PR[4]=0 }
}\
{ if (PR[5]=="")
{PR[5]=0 }
}\
{ if (PR[6]=="")
{PR[6]=0 }
}\
{ print $1,$2,QTY[1],QTY[2],QTY[3],QTY[4],QTY[5],QTY[6],PR[1],PR[2],PR[3],PR[4],PR[5],PR[6] }'\
file1.csv > file2.csv

Issue 1
This delivers a correct file output if the original line had a full complement of data, like lines 1 and 3 above. However it fails for line 2, because there is nothing in $4.
I want to check if $4 exists for a line, and if not replace the PR fields with a zero.

Issue 2
Sometimes the $4 will have less than six fields. Therefore I want to count the number of fields in $4 and append zero value fields to pad it out.

Issue 3
Obviously the series of if statements above isn't very elegant. Ideally I would like to do that in a loop where I count the number of fields and check them all sequentially.

Hope there is someone who likes this sort of challenge!!