Manipulating Pick multi dimensional data with awk.

Shell Programming and Scripting
1

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!!

2

What should be the desired output of the data file above?

Regards

3

Hi Franklin. I guess that would have been helpful!

I am looking for the following;

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,0,0,0,0,0,0,
ITEMS,DESCRIPTION OF CARS,24,99,499,999,2999,99999,0,0,0,0,17.62,0.00,
BAGS,DESCRIPTION OF BAGS,24,99,499,999,4999,99999,12.30,0,0,0,0,0,

My script delivers this when there are full lines, but not when there are not.

Hope you can help.

4

According to your first post you said that your script fails for the second line but your script gives the output as above with the given example of your first post, so what's wrong?

5

Uhh, I think you missed this one.....

You asked me what the desired output was. I therefore showed you the desired output, not the actual one.

The actual output I am getting 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,0,0,0,0,17.62,0.00,
BAGS,DESCRIPTION OF BAGS,24,99,499,999,4999,99999,12.30

6

Ok, try this:

awk 'BEGIN{FS=OFS=","}
{
  gsub("]", ",")
  $15=""
  for(i=1;i<NF;i++) {
    if($i=="") {
      $i="0"
    }
  }
  {print}
}' file1.csv > file2.csv

This is what I get:

$ cat file1.csv
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]]
$
$ awk 'BEGIN{FS=OFS=","}
{
  gsub("]", ",")
  $15=""
  for(i=1;i<NF;i++) {
    if($i=="") {
      $i="0"
    }
  }
  {print}
}' file1.csv
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,0,0,0,0,0,0,
ITEMS,DESCRIPTION OF CARS,24,99,499,999,2999,99999,0,0,0,0,17.62,0.00,
BAGS,DESCRIPTION OF BAGS,24,99,499,999,4999,99999,12.30,0,0,0,0,0,
$

Regards

7

Thanks Franklin. I had got a little further on my own (I'm in a different time zone), but your way is much better and is much more elegant.
I appreciate your help. :slight_smile: