Manipulating Pick multi dimensional data with awk. Post: 302332033

Sponsored Content

Top Forums Shell Programming and Scripting Manipulating Pick multi dimensional data with awk. Post 302332033 by mike.strategis on Wednesday 8th of July 2009 03:03:45 AM

07-08-2009

Registered User

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

mike.strategis

View Public Profile for mike.strategis

Find all posts by mike.strategis

9 More Discussions You Might Find Interesting

1. 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...

2. Shell Programming and Scripting

Multi Dimensional array in KSH

Is there any way to use multi dim. array in KSH ?

3. 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... ...

4. 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

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

6. 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...

7. 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,...

8. 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"...

9. 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 ...

LEARN ABOUT REDHAT

filechan

FILECHAN(8)						      System Manager's Manual						       FILECHAN(8)

NAME

       filechan - file-writing backend for InterNetNews

SYNOPSIS

       filechan [ -d directory ] [ -f fields ] [ -m mapfile ] [ -p pidfile ]

DESCRIPTION

       Filechan reads lines from standard input and copies certain fields in each line into files named by other fields within the line.  Filechan
       is intended to be called by innd(8) as a channel feed.  (It is not a full exploder and does not accept commands;  see  newsfeeds(5)  for  a
       description of the difference and buffchan(8) for an exploder program.)

       Filechan  input	is  interpreted  as a set of lines.  Each line contains a fixed number of initial fields, followed by a variable number of
       filename fields.  All fields in a line are separated by whitespace.  The default number of initial fields is one.

       For each line of input, filechan writes the initial fields, separated by whitespace and followed by a newline, to each of the  files  named
       in the filename fields.	When writing to a file, filechan opens it in append mode and tries to lock it and change the ownership to the user
       and group who owns the directory where the file is being written.

OPTIONS

       -f     The ``-f'' flag may be used to specify a different number of initial fields.

       -d     By default, filechan writes its output into the directory <pathoutgoing in inn.conf>.  The ``-d'' flag may  be  used  to	specify  a
	      directory the program should change to before starting.

       -p     If the ``-p'' flag is used, the program will write a line containing its process ID (in text) to the specified file.

       If filechan is invoked with ``-f 2'' and given the following input:
	      news/software/b/132 <1643@munnari.oz.au> foo uunet
	      news/software/b/133 <102060@litchi.foo.com> uunet munnari
	      comp/sources/unix/2002 <999@news.foo.com> foo uunet munnari

       Then the file foo will have these lines:
	      news/software/b/132 <1643@munnari.oz.au>
	      comp/sources/unix/2002 <999@news.foo.com>

       the file munnari will have these lines:
	      news/software/b/133 <102060@litchi.foo.com>
	      comp/sources/unix/2002 <999@news.foo.com>

       and the file uunet will have these lines:
	      news/software/b/132 <1643@munnari.oz.au>
	      news/software/b/133 <102060@litchi.foo.com>
	      comp/sources/unix/2002 <999@news.foo.com>

       Because	the time window in which a file is open is very small, complicated flushing and locking protocols are not needed; a mv(1) followed
       by a sleep(1) for a couple of seconds is sufficient.

       -m     A map file may be specified by using the ``-m'' flag.  Blank lines and lines starting with a number sign (``#'') are  ignored.   All
	      other  lines  should have two host names separated by a colon.  The first field is the name that may appear in the input stream; the
	      second field names the file to be used when the name in the first field appears.	For example, the following map file may be used to
	      map the short names above to the full domain names:
	      # This is a comment
	      uunet:news.uu.net
	      foo:foo.com
	      munnari:munnari.oz.au

HISTORY

       Written by Robert Elz <kre@munnari.oz.au>, flags added by Rich $alz <rsalz@uunet.uu.net>.  This is revision 1.6, dated 1998/04/09.

SEE ALSO

       buffchan(8), inn.conf(5), innd(8), newsfeeds(5).

																       FILECHAN(8)