Filter rows in a file based on composite key in another file

Here is a quick, simple and easy way to filter the records in a file based on a composite key present in another file!


There are many ways to do it, this method uses ask, grep & sort to do the job

Assumptions:

1. Files are pipe ("|") separated
2. File containing the key is called "accounts"
3. Columns 2 & 4 join to form the key to be used for filtering
4. File containing the records to be filtered is called "acdata"
5. This file contains the "key" column apart from other data
6. Filtered records need to be stored in the file "filtdata"

awk 'BEGIN { FS="|";} {k1=$2; k2=$4; k=k1 k2; print "grep " k  " acdata" ;}' accounts | sort -u | bash > filtdata

Sort with unique option turned on needs to be used to ensure that we get only one row from the key file in case there are more than one rows with the same composite key.

Convert Fixed width file to Delimited in Unix

Needed to convert a Convert Fixed width file to Delimited recently. There are many solutions, but the simplest & best solutions involved using the tools available in Unix / Linux. Here are a few possible ways to do it on Unix / Linux.



Input File: fixed.txt
The column widths are 2, 4, 6 & 3

AABBBBCCCCCCDDD
EEFFFFGGGGGGHHH
IIJJJJKKKKKKLLL
MMNNNNOOOOOOPPP
QQRRRRSSSSSSTTT
UUVVVVWWWWWWXXX
YYZZZZAAAAAABBB

Output File: delim.csv

AA,BBBB,CCCCCC,DDD
EE,FFFF,GGGGGG,HHH
II,JJJJ,KKKKKK,LLL
MM,NNNN,OOOOOO,PPP
QQ,RRRR,SSSSSS,TTT
UU,VVVV,WWWWWW,XXX
YY,ZZZZ,AAAAAA,BBB

Using GAWK on Linux

gawk < fixed.txt 'BEGIN{ FIELDWIDTHS = "2 4 6 3"; OFS=",";}{print $1,$2,$3,$4}' > delim.csv

This solution defines field widths & field separator and then uses gawk to split each line. The only down side is that GAWK is available only on Linux or GNU based systems.


Using SED on *nix

sed -e 's/./&,/2' -e 's/./&,/7' -e 's/./&,/14' fixed.txt > delim.csv

This solution uses sed - a stream editor to substitute commas inline.
The -e is to tell sed to execute the next command line argument as a sed program and you can have multiple such commands.
The s/./ looks to substitute any character.
The &, denotes the matched string followed by a comma that we need.
The /2 tells sed to do this substitution for only the first 2 such occurrences.
So in effect, a comma is put in after the first 2 characters are matched. Note that since this is substituting inline, the insertion point for the next comma is at position 2 + 1 + 4 = 7!

Here is a link to a very good list of handy sed one-liners on linuxhowtos.org.


Using AWK on Unix

1. Create a file conversion_rules

{
 a[1] = 2;
 a[2] = 4;
 a[3] = 6;
 a[4] = 3;
 }
 {
        o = "";
        r = 1;
        for (i=1;i<length(a);i++)
        { o = o  substr($0,r,a[i]) ","; r = r + a[i]; }
        o = o  substr($0,r,a[length(a)])
        print o;
 }

2. Run the command:

awk -f conversion_rules fixed.txt > delim.csv

This solution uses plain awk to loop through each line and add comma after the defined column length. An awk program file is created to define the column lengths and the looping & comma insertion logic. This is fed to an awk instance along with the input file and the output is redirected to the required file.