Manipulating Pick multi dimensional data with awk.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Manipulating Pick multi dimensional data with awk.
# 1  
Old 07-08-2009
Manipulating Pick multi dimensional data with awk.

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  
Old 07-08-2009
What should be the desired output of the data file above?

Regards
# 3  
Old 07-08-2009
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  
Old 07-09-2009
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  
Old 07-09-2009
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  
Old 07-10-2009
Ok, try this:

Code:
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:

Code:
$ 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  
Old 07-11-2009
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. :-)
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Multi Dimensional array in bash

Hi, I'm developing a script which contains a multi dimensional array, however for some reason the array is not iterating. When executing the script, services are listed as arguments from argument 2. Ex voice data sms. service=${@:2}; for services in $service do ... (2 Replies)
Discussion started by: nms
2 Replies

2. Shell Programming and Scripting

Multi Dimensional array

I have an array of names. Each one of the name, has a number represented to it. For example A has an ID 8, B has an ID 2. What I am after is a for loop that when the array is in position 1, a particular variable is set to the value of position 1 in array 2 declare -a arr=("A" "B" "C"... (6 Replies)
Discussion started by: nms
6 Replies

3. Shell Programming and Scripting

Manipulating xml data with awk

Hi everyone, I have a little bit of complicated task to finish with AWK. Here it is; I have a data file in xml format which looks like this <data> a1 a2 a3 a4 a5 b1 b2 b3 b4 b5 c1 c2 c3 c4 c5 d1 d2 d3 d4 d5 e1 e2 e3 e4 e5 </data> lets say each data block contains 5 rows and 5 columns,... (13 Replies)
Discussion started by: hayreter
13 Replies

4. Shell Programming and Scripting

Manipulating a list into a two-dimensional array

hi, total newbie to shell scripting and wondering if some of you guru's can give me a hand on a problem I'm trying to solve. The tmplsnr.a file contains LSNR_51526 db1 db2 LSNR_51527 db3 db4 db5 Summary - depending on which db is set, the script will start the relevant listener... (5 Replies)
Discussion started by: mingy10
5 Replies

5. Shell Programming and Scripting

How to pick a group of data using awk/ksh

Hi gurus, I have data coming in as shown below. And in each case, I need to pick the data in the last group as shown below. Data Set 1: DC | 18161621 LA | 15730880 NY | 16143237 DC | 18161621 LA | 17316397 NY | 17915905 DC | 18161621 LA | 17993534 NY | 18161621 DC | 18161621... (11 Replies)
Discussion started by: calredd
11 Replies

6. Shell Programming and Scripting

sorting multi dimensional array

Hi there, Can someone let me know how to sort the 2 dimensional array below by column 1 then by column 2? 22 55 2222 2230 33 66 44 58 222 240 11 25 22 60 33 45 output: 11 25 22 55 22 60 33 45 33 66 44 58 (6 Replies)
Discussion started by: phoeberunner
6 Replies

7. Shell Programming and Scripting

Match elements in an AWK multi-dimensional array

Hello, I have two files in the following format; file1: A B C D E F G H I J K L file2: 1 2 3 4 5 6 7 8 9 10 11 12 I have read them both in to multi-dimensional arrays. I need a file that has column 2 of the first file printed out for each column 3 of the second file ie... ... (3 Replies)
Discussion started by: cold_Que
3 Replies

8. Shell Programming and Scripting

Multi Dimensional array in KSH

Is there any way to use multi dim. array in KSH ? (1 Reply)
Discussion started by: sinpeak
1 Replies

9. Programming

Multi-Dimensional Arrays

So, I'm fooling around with multi demtional arrays, and I made this in a short amount of time: #include <stdio.h> main(int argc, char *argv) { char blah = { {'a', 'b'}, {'b', 'a'} }; int i = 0; while (i < 2) { if (argv == blah) printf("%c\n", blah); i++; } } The goal... (3 Replies)
Discussion started by: Octal
3 Replies
Login or Register to Ask a Question

Featured Tech Videos