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!!
9 More Discussions You Might Find Interesting
1. Programming
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
2. Shell Programming and Scripting
Is there any way to use multi dim. array in KSH ? (1 Reply)
Discussion started by: sinpeak
1 Replies
3. Shell Programming and Scripting
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
4. Shell Programming and Scripting
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
5. Shell Programming and Scripting
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
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
7. Shell Programming and Scripting
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
8. Shell Programming and Scripting
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
9. Shell Programming and Scripting
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