Shell Programming and Scripting

Need awk solution. Please advise.

inputfile.txt

Code:

1,NY, 1111
2,MI, 222
3,NY,333
4,OH,444
5,OH,555

mapping.txt

Code:

NY      NYNY
IL        ILLINOIS
OH       OHIO

Need to write a code which will compare 2nd column of inputfile.txt with mapping file and redirect output based on the mapping.txt file.
eg: OH will be mapped to OHIO and will be redirect to OHIO.txt. Mapping not found will be redirected to notfound.txt file

output would look like
-------------------------------
NYNY.txt

Code:

1,NY, 1111
3,NY,333

-------------------------------
OHIO.txt

Code:

4,OH,444
5,OH,555

-------------------------------
notfound.txt

Code:

2,MI, 222

couldnt test but something like this should work..

Code:

awk 'NR==FNR{A[$1]=$2;next}
{FS=",";if(A[$2]){print $0>A[$2]".txt"}else{print $0>"notfound.txt"}}' mapping.txt inputfile.txt

This should come close, but the 1st line in the 2nd file will be split into fields using the wrong value for FS (since field splitting will be done before FS is reset). Assuming that there could be around 50 output files, you will also need to close files to keep from running out of file descriptors. (And, if a file is opened multiple times, the redirection operator needs to be >> instead of >. But, this also means that the state output files need to be manually removed before the script is rerun unless you want multiple runs to accumulate data in the output files instead of replacing the data each time the script is run. The script below could be modified to remove or truncate the output files while it is reading mapping.txt.) Anyway, this alternative may help you find something that works for your input:

Code:

awk '
FNR == NR {
        sf[$1] = $2
        next
}
{       if(lf != sf[$2]) {
                if(lf != "") close(of)
                lf = sf[$2] ? sf[$2] : "notfound"
                of = lf ".txt"
        }
        print >> of
}' mapping.txt FS=',' inputfile.txt

If you are using a Solaris/SunOS system, use /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk instead of /usr/bin/awk or /bin/awk.