Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

UNIX append field with comparing fields from multiple column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting UNIX append field with comparing fields from multiple column
# 1  
Old 09-10-2013
Question UNIX append field with comparing fields from multiple column
I have a csv dump from sql server that needs to be converted so it can be feed to another program. I already sorted on field 1 but there are multiple columns with same field 1 where it needs to be compared against and if it is same then append field 5.

i.e from

Code:
ANG SJ,0,B,LC22,LC22(0)
BAT SJ,0,B,LC22,LC22(0)
BAT SJ,0,B,LC22,LC22(0)
BGA SJ,40000,B,SL22,SL22(40000)
BGA SJ,40000,B,SL22,SL22(40000)
BGA SJ,50000,B,SL22,SL22(50000)
BHW PW,152000,B,EX22,EX22(152000)
BKIR ID,15000000,B,GSI,GSI(15000000)
BKIR ID,15000000,B,GSI,GSI(15700000)
BKIR ID,15000000,B,GSI,GSI(15000000)
BKIR ID,15900000,B,GSI,GSI(15900000)
EBS AV,0,B,LC22,LC22(0)
EBS AV,1,B,LC22,LC22(1)
EBS AV,2,B,LC22,LC22(2)
EBS AV,3,B,LC22,LC22(3)
EBS AV,4,B,LC22,LC22(4)

it needs to be converted to following:

Code:
ANG SJ,0,B,LC22,LC22(0)
BAT SJ,0,B,LC22(0)|LC22(0)
BGA SJ,40000,B,SL22(40000)|SL22(40000)|SL22(50000)
BHW PW,152000,B,EX22,EX22(152000)
BKIR ID,15000000,B,GSI(15000000)|GSI(15700000)|(15000000)|(15900000)
EBS AV,0,B,LC22(0)|LC22(1)|LC22(2)|LC22(3)|LC22(4)

.

problem is sometime there is single filed 1 or it could be two or three or four times.
Last edited by nike27; 09-10-2013 at 01:37 PM.. Reason: fix typo
# 2  
Old 09-10-2013
Try something like
Code:
awk '{A[$1]=!C[$1]++? $0 : A[$1] OFS $NF} END{for(i in A) print A[i]}' FS=, OFS=\| file

This User Gave Thanks to Scrutinizer For This Post:
nike27
# 3  
Old 09-10-2013
Thanks a lot.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How can I stack multiple (>1000) fields into one continuous field?

Hi, I'm struggling with a problem at the minute. Basically, I have a file with >1000 columns / fields (with headers), each containing a variable number of values. I would like to stack each column such that all the data appears in a single column, e.g. the first value of column 2 is moved to... (5 Replies)
Discussion started by: TAlcock
5 Replies

2. Shell Programming and Scripting

Append data with substring of nth column fields using awk

Hi guys, I have problem to append new data at the end of each line of the files where it takes whole value of the nth column. My expected result i just want to take a specific value only. This new data is based on substring of 11th, 12th 13th column that has comma seperated value. My code: awk... (4 Replies)
Discussion started by: null7
4 Replies

3. Shell Programming and Scripting

Single Field to multiple fields searching

I have a fileA with one column (1000 rows), and fileB with 26 columns(13000 rows). I need to search each value of fileA with fileB and return all the 26 values from FileB to a new file- File C if matches. The search value (from FileA) may present in any of the 26 values in FileB. This value is not... (7 Replies)
Discussion started by: vamsikrishna928
7 Replies

4. Shell Programming and Scripting

Awk: print lines with one of multiple pattern in the same field (column)

Hi all, I am new to using awk and am quickly discovering what a powerful pattern-recognition tool it is. However, I have what seems like a fairly basic task that I just can't figure out how to perform in one line. I want awk to find and print all the lines in which one of multiple patterns (e.g.... (8 Replies)
Discussion started by: elgo4
8 Replies

5. Shell Programming and Scripting

Join fields comparing 4 fields using awk

Hi All, I am looking for an awk script to do the following Join the fields together only if the first 4 fields are same. Can it be done with join function in awk?? a,b,c,d,8,,, a,b,c,d,,7,, a,b,c,d,,,9, a,b,p,e,8,,, a.b,p,e,,9,, a,b,p,z,,,,9 a,b,p,z,,8,, desired output: ... (1 Reply)
Discussion started by: aksijain
1 Replies

6. UNIX for Dummies Questions & Answers

Comparing multiple fields from 2 files uing awk

Hi I have 2 files as below File 1 Chr Start End chr1 120 130 chr1 140 150 chr2 130 140 File2 Chr Start End Value chr1 121 128 ABC chr1 144 149 XYZ chr2 120 129 PQR I would like to compare these files using awk; specifically if column 1 of file1 is equal to column 1 of file2... (7 Replies)
Discussion started by: sshetty
7 Replies

7. Shell Programming and Scripting

Compare a common field in two files and append a column from File 1 in File2

Hi Friends, I am new to Shell Scripting and need your help in the below situation. - I have two files (File 1 and File 2) and the contents of the files are mentioned below. - "Application handle" is the common field in both the files. (NOTE :- PLEASE REFER TO THE ATTACHMENT "Compare files... (2 Replies)
Discussion started by: Santoshbn
2 Replies

8. Shell Programming and Scripting

awk- comparing fields from the same column, finding discontinuities.

Hello, I have a file with two fields. The first field repeats itself for quite a while but the second field changes. What I want to do is to go through the first column until its value changes (and while it doesn't, verify that the second field is in a sequence from 0-15). Example input: ... (13 Replies)
Discussion started by: acsg
13 Replies

9. Shell Programming and Scripting

eAdd two fields in a column if their previous field values are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output file as follows If field3 in file1 is same as field3 in the next line then the field4 should add... (1 Reply)
Discussion started by: yerruhari
1 Replies

10. Shell Programming and Scripting

How to read and compare multiple fields in a column at the same time

Hi, Currently I am coding up a nasty way of reading file input using *cat* rather than *read*. My text input looks like TextA 100 TextB 110 TextC 120 Currently I am using cat |while read line to read the first column and second column fields. cat foo.txt|while read line do ... (1 Reply)
Discussion started by: ahjiefreak
1 Replies
Login or Register to Ask a Question