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

Sponsored Content

Top Forums Shell Programming and Scripting Matching column value from 2 different file using awk and append value from different column Post 303003460 by RudiC on Thursday 14th of September 2017 05:31:39 AM

09-14-2017

Registered User

It's by sheer coincidence that you get anything from that code snippet. The field separator should be set to comma, but due to an incorrect syntax it isn't. The a array holds one single element only, indexed by the empty string, and is overwritten repeatedly until the first file's last line is encountered. As $13 doesn't exist in file 2 and thus is empty, that only element of a is printed. You seem to want to ignore the commas inside the double quotes, so the entire quoted string will be considered field 1? If so, they need to be replaced upfront, and then converted back after the operations.
Would this come close to what you need:

Code:

awk -F\" '{for (i=2; i<=NF; i+=2) gsub (/,/, "\001", $i)} 1' OFS=\" file2 |
awk -F',' 'NR==FNR {a[$2]=$1; next } {print $0, a[$2] }' - file1 |
awk -F\" '{for (i=2; i<=NF; i+=2) gsub ("\001", ",", $i)} 1' OFS=\" 
HUAWEI,20LMG011_DEKET_1296_RTN-980_IDU-1-11-ISV3-1(to LAMONGAN_M),East_Java,20LMG011_DEKET_1296_RTN-980_IDU-1,20LMG011,20LMG 027_1287_LAMONGAN_RTN980_IDU1,20LMG027,1+1(HSB),195.675,20LMG011-20LMG027,99.9995,202.6952012 "10.200.6.69_NE=3147930,S=11,CP=-1,PP=16||1|"
HUAWEI,20LMG011_DEKET_1296_RTN-980_IDU-1-13-ISV3-1(to LAMONGAN_S),East_Java,20LMG011_DEKET_1296_RTN-980_IDU-1,20LMG011,20LMG 027_1287_LAMONGAN_RTN980_IDU1,20LMG027,1+1(HSB),195.675,20LMG011-20LMG027,99.9995,202.6952012 
HUAWEI,20LMG027_1287_LAMONGAN_RTN980_IDU1-11-ISV3-1(to DEKET_M),East_Java,20LMG027_1287_LAMONGAN_RTN980_IDU1,20LMG027,20LMG0 11_DEKET_1296_RTN-980_IDU-1,20LMG011,1+1(HSB),195.675,20LMG027-20LMG011,99.9995,202.6952012

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

xls2csv

xls2csv(1)						      General Commands Manual							xls2csv(1)

NAME

       xls2csv - reads MS-Excel file and puts its content as comma-separated data on standard output

SYNOPSIS

       xls2csv [-xlV] [-f  format ] [-b  string ] [-s  charset ] [-d  charset ] [-q  number ] [-c  char] files

DESCRIPTION

       xls2csv	reads  MS-Excel  spreadsheet  and  dumps  its content as comma-separated values to stdout. Numbers are printed without delimiters,
       strings are enclosed in the double quotes. Double-quotes inside string are doubled.

OPTIONS

       -x      print unknown Unicode chars as xNNNN, rather than as question marks

       -l      list known charsets and exit successfully

       -cchar  cell separator char. By default - comma.

       -bstring
	       sheet break string. This string (by default - formfeed) would be output at the end of each workbook page. This  string  is  printed
	       after page starting at start of line, but no linefeed would be automatically added at the end of string. Include newline at the ent
	       of sheet separator if you want it to appear on separate line by itself

       -gnumber number of decimal digits in the numbers. By default maximal double precision (system-dependent macro DBL_DIG) is used.

       -qnumber
	       set quote mode. In quote mode 0 cell contents is never quoted.  In quote mode 1 only strings which contain spaces, double quotes or
	       commas are quoted.  In quote mode 2 (default) all cells with type string are quoted.  In quote mode 3 all cells are quoted.

       -dcharset`
	       -  specifies  destination  charset  name.  Charset file has format described in CHARACTER SETS section of catdoc(1) manual page. By
	       default, current locale charset would be used if langinfo support was enabled at the compile time.

       -scharset
	       - specifies source charset. Typically, Excel files have CODE PAGE record, which denotes input charset, but for some reason you  may
	       wish to override it.

       -fformat
	       - specifies date/time format to use for output of all Excel date and time  values.  If this option is not specified, format, speci-
	       fied in the spreadsheet is used. On POSIX system any format, allowed by strftime(3) can be used as value of this option. Under  MS-
	       DOS xls2csv implements limited set of strftime formats, namely m, d, y, Y, b, l, p, H, M, S.

       -V      outputs version number

FILES

       ${HOME}/.catdocrc, catdoc charset files and substitution map files (see catdoc(1) manual page for details,

SEE ALSO

       cat(1), catdoc(1), strings(1), utf8(7), unicode(7)

AUTHOR

       V.B.Wagner <vitus@45.free.net>, based on biffview by David Rysdam

MS-Word reader							  Version 0.94.4							xls2csv(1)