Comma delimited files

[Kenton]

Hi all,

Just wondering what the best way is to select particular fields from particular records in comma delimited data files.

say I have the file file1.dat
1,header
2,123,23,1,,1,1,5
2,124,23,1,,1,3,9
2,125,23,1,,2,1,5
9,footer

and I want to create a file file2.dat containing only records from file1.dat where field1 = '2' and field6 = '1'
and I only want fields 2,3,6,7 & 8

Thus file2.dat
123,23,1,1,5
124,23,1,3,9

Thanks
Kenton

 

[toolkit]

One way:

awk -F',' '
$1 == 2 {
  for( i = 2; i < NF; i++ ) {
    printf &quot;%s,&quot;, $i
  }
  print $NF
}' < test.txt

Or another way:

perl -n -a -F',' -e '$&quot;=&quot;,&quot;; shift @F, print &quot;@F&quot; if $F[0] == 2' < test.txt

Cheers, Neil

 

[bigoldbulldog]

Try out

awk 'BEGIN {FS=","; OFS=","}
$1 == 2 && $6 == 1 {
print $2,$3,$6,$7,$8
}' < file1.dat > file2.dat

Cheers,
ND

bigoldbulldog@hotmail.com

 

[Kenton]

Thanks Guys

I went with the awk 'BEGIN .... solution and it works great.

Now
using this awk soution, how do I substitute $7 for a list of values
ie 1 = Fred, 2 = Mike, 3= John

So instead of
123,23,1,1,5
124,23,1,3,9

I now get
123,23,1,Fred,5
124,23,1,John,9

Sorry for being a dummy
Kenton

 

[toolkit]

ps: sorry my example was incomplete - should have read your posting in its entirety :-(

 

[Kenton]

Sorry, got it sorted

Thanks Heaps

 

[bigoldbulldog]

If you are going to map to names then this should work. There are a million ways of course.

BEGIN {
FS=&quot;,&quot;
OFS=&quot;,&quot;
name[1]=&quot;Fred&quot;
name[2]=&quot;Mike&quot;
name[3]=&quot;John&quot;
}
$1 == 2 && $6 == 1 {
print $2,$3,$6,name[$7],$8
}

Alternatively, the name mappings may come from somewhere else like a file or stdin with some sort of codelist. Awk could read in this auxillary file or better yet try out unix's join command.

Example with join have a map file that is sorted on the key field. i.e.

1,Fred
2,Mike
3,John

then run via the awk script from my first post and pipe to the join command and then redirect to your output file or wherever.

awk 'BEGIN {FS=&quot;,&quot;; OFS=&quot;,&quot;}
$1 == 2 && $6 == 1 {
print $2,$3,$6,$7,$8
}' < file1.dat |
join -j1 4 -j2 1 -o 1.1 1.2 1.3 2.2 1.5 -t&quot;,&quot; - mapfile > file2.dat

Cheers,
ND

bigoldbulldog@hotmail.com