Matching column value from 2 different file using awk and append value from different column Post: 303003472

Sponsored Content

Top Forums Shell Programming and Scripting Matching column value from 2 different file using awk and append value from different column Post 303003472 by RudiC on Thursday 14th of September 2017 07:51:12 AM

09-14-2017

Registered User

Code:

awk -F\" '                                      # set field sep. to "
        {for (i=2; i<=NF; i+=2) \               # in every second (= inside quotes) field
         gsub (/,/, "\001", $i)                 # replace the comma with ^A (a placeholder)
        }
1                                               # print the modified line
' OFS=\" file2 |                                # set output field sep. to " (important!)
awk -F',' '                                     # set FS to comma
NR==FNR {a[$2] = $1                             # save $1 (text to append) to array indexed by $2
         next                                   # stop processing the actual line; goto next
        }
        {print $0, a[$2] }                      # print file2's line, followed by the app. txt, if exists 
' - file1 |                                     # work on input stream, i.e. stdin ("-", piped in) and file1
awk -F\" '                                      # undo comma replacements as in step 1
        {for (i=2; i<=NF; i+=2) \
                 gsub ("\001", ",", $i)
        }
1
' OFS=\"

This User Gave Thanks to RudiC For This Post:

RudiC

View Public Profile for RudiC

Find all posts by RudiC

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Awk: Need help replacing a specific column in a file by part of a column in another file

Hi, I have two input files as File1 : ABC:client1:project1 XYZ:client2-aa:project2 DEF:client4:proj File2 : client1:W-170:xx client2-aa:WT-04:yy client4:L-005A:zz Also, array of valid values can be hardcoded like Output : ABC:W:project1 XYZ:WT:project2

2. UNIX for Dummies Questions & Answers

append column and row header to a file in awk script.

Hi! Is there a way to append column and row header to a file in awk script. For example if I have Jane F 39 manager Carlos M 40 system administrator Sam F 20 programmer and I want it to be # name gend age occup 1 Jane F 39 manager 2 Carlos M ...

3. Shell Programming and Scripting

Append column using awk/nawk

Is there any way I can achieve this? Considering test1 : a 1 2 3 4 b 2 3 4 5 c 12 1232 14 1 d 10 13 4 5 e 1 5 6 9 1 And test to be some string : qw twe tew we qw I want something like this : a 1 2 qw 4 b 2 3 twe 5

4. Shell Programming and Scripting

Find lines with matching column 1 value, retain only the one with highest value in column 2

I have a file like: I would like to find lines lines with duplicate values in column 1, and retain only one based on two conditions: 1) keep line with highest value in column 3, 2) if column 3 values are equal, retain the line with the highest value in column 4. Desired output: I was able to...

5. Shell Programming and Scripting

How to awk or grep the last column in file when date on column contains spaces?

Hi have a large spreadsheet which has 4 columns APM00111803814 server_2 96085 Corp IT Desktop and Apps APM00111803814 server_2 96085 Corp IT Desktop and Apps APM00111803814 server_2 96034 Storage Mgmt Team APM00111803814 server_2 96152 GWP...

6. Shell Programming and Scripting

Solution for replacement of 4th column with 3rd column in a file using awk/sed preserving delimters

input "A","B","C,D","E","F" "S","T","U,V","W","X" "AA","BB","CC,DD","EEEE","FFF" required output: "A","B","C,D","C,D","F" "S", T","U,V","U,V","X" "AA","BB","CC,DD","CC,DD","FFF" tried using awk but double quotes not preserving for every field. any help to solve this is much...

7. Shell Programming and Scripting

Matching column then append to existing File as new column

Good evening I have the below requirements, as I am not an experts in Linux/Unix and am looking for your ideas how I can do this. I have file called file1 and file2. I need to get the second column which is text1_random_alphabets and find that in file 2, if it's exists then print the 3rd...

8. Shell Programming and Scripting

awk to Sum columns when other column has duplicates and append one column value to another with Care

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For...

9. Shell Programming and Scripting

awk script to append suffix to column when column has duplicated values

Please help me to get required output for both scenario 1 and scenario 2 and need separate code for both scenario 1 and scenario 2 Scenario 1 i need to do below changes only when column1 is CR and column3 has duplicates rows/values. This inputfile can contain 100 of this duplicated rows of...

10. UNIX for Beginners Questions & Answers

Compare 1st column from 2 file and if match print line from 1st file and append column 7 from 2nd

hi I have 2 file with more than 10 columns for both 1st file apple,0,0,0...... orange,1,2,3..... mango,2,4,5..... 2nd file apple,2,3,4,5,6,7... orange,2,3,4,5,6,8... watermerlon,2,3,4,5,6,abc... mango,5,6,7,4,6,def....

LEARN ABOUT DEBIAN

masqmail.aliases

masqmail.aliases(5)						   File Formats 					       masqmail.aliases(5)

NAME

       masqmail.aliases - masqmail alias file format

DESCRIPTION

       This man page describes the format of the masqmail alias file.  Its usual location is /etc/aliases.

FILE FORMAT

       The  alias  file consists of lines of the form: local_part: item1, item2, ...  Items can be surrounded by double quotes `"'.  If within the
       quotes other quotes are needed for an address they can be escaped with a leading backslash `'.

       A leading backslash `' indicates that this address shall not be further expanded.

       A leading pipe symbol `|' indicates that the item shall be treated as a pipe command.  The content of the message will then be sent to  the
       standard  input	of a command.  The command will run under the user id and group id masqmail is running as.  If quotes are needed, the pipe
       symbol must appear within the quotes.

       Loops will be detected, the offending address will be ignored.

       Aliases will be expanded at delivery time.  This means that if there is a message still in the queue and you change any alias which matches
       one of the recipient addresses, the change will have effect next time a delivery is attemped.

       There is no need to restart masqmail or run any command when the alias file has been changed.

AUTHOR

       Masqmail was written by Oliver Kurth.  It is now maintained by Markus Schnalke <meillo@marmaro.de>.

       You  will find the newest version of masqmail at http://marmaro.de/prog/masqmail/.  There is also a mailing list, you will find information
       about it at masqmail's main site.

BUGS

       Please report bugs to the mailing list.

SEE ALSO

       masqmail.conf(5), masqmail(8),

masqmail-0.2.30 						    2011-07-27						       masqmail.aliases(5)