Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Matching and Merging csv data fields based on a common field

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Matching and Merging csv data fields based on a common field
# 1  
Old 02-03-2012
Java Matching and Merging csv data fields based on a common field
Dear List,

I have a file of csv data which has a different line per compliance check per host. I do not want any omissions from this csv data file which looks like this:
Code:
date,hostname,status,color,check
02-03-2012,COMP1,FAIL,Yellow,auth_pass_change
02-03-2012,COMP1,FAIL,Yellow,auth_pass_change
02-03-2012,COMP1,PASS,Red,auth_pass_dictionary
02-03-2012,COMP1,PASS,Yellow,auth_pass_change
02-03-2012,SERVER2,PASS,Yellow,auth_pass_change
02-03-2012,SERVER2,FAIL,Yellow,auth_pass_change
02-03-2012,SERVER2,FAIL,Yellow,user_home_files

The second file has a unique field(field 2) being the hostname whereby the first field of this data is the customer_account as follows:
Code:
customer_account,hostname
CLIENT1,COMP1
CLIENT2,DESK3
CLIENT3,FIRE1
CLIENT4,SERVER2
cLIENT2,STATION1

What I am trying to achieve is match the hostname in the 2nd file with the hostname in the first file and merge the corresponding customer_account field from the second file into the first file as follows:
Code:
date,hostname,status,color,check,customer_account
02-03-2012,COMP1,FAIL,Yellow,auth_pass_change,CLIENT1
02-03-2012,COMP1,FAIL,Yellow,auth_pass_change,CLIENT1
02-03-2012,COMP1,PASS,Red,auth_pass_dictionary,CLIENT1
02-03-2012,COMP1,PASS,Yellow,auth_pass_change,CLIENT1
02-03-2012,SERVER2,PASS,Yellow,auth_pass_change,CLIENT4
02-03-2012,SERVER2,FAIL,Yellow,auth_pass_change,CLIENT4
02-03-2012,SERVER2,FAIL,Yellow,user_home_files,CLIENT4


Does anyone know of any solutions?

Thanks!

Land
Last edited by Franklin52; 02-03-2012 at 02:00 PM.. Reason: Please use code tags for data and code samples, thank you
# 2  
Old 02-03-2012
Code:
awk 'NR==FNR{a[$2]=$1;next}{print $0","a[$2];}' FS="," file2 file1

Guru.
This User Gave Thanks to guruprasadpr For This Post:
landossa
# 3  
Old 02-07-2012
work perfectly, thanks guru!
# 4  
Old 04-24-2012
Sorry to bring back this thread, this is exactly what i needed, with just a little change:

Same functionality as the above example, but i want to add "NONE" if no common field was found.

Thanks for the help Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Merging two files based on matching columns

Hi, I am facing issues while accomplishing below task. We have two files Test1.txt and Test2.txt. We have to match 1st column of Test1.txt file with 2nd column of Test2.txt and then merge 2nd file with the 1st file. In the output we should select column 1 and 2 from the 1st file and column 1... (5 Replies)
Discussion started by: Prathmesh
5 Replies

2. Shell Programming and Scripting

Merging fields in CSV

Hi experts, I have a csv file which has one field (ID) repeated multiple times with corresponding other field values. I need to convert this file in a format where for a ID all other values has to be present in single field. For Eg : Here in below file ID 1 is repeated 3 times with different... (7 Replies)
Discussion started by: bharathbangalor
7 Replies

3. Shell Programming and Scripting

Merging two special character separated files based on pattern matching

Hi. I have 2 files of below format. File1 AA~1~STEVE~3.1~4.1~5.1 AA~2~DANIEL~3.2~4.2~5.2 BB~3~STEVE~3.3~4.3~5.3 BB~4~TIM~3.4~4.4~5.4 File 2 AA~STEVE~AA STEVE WORKS at AUTO COMPANY AA~DANIEL~AA DANIEL IS A ELECTRICIAN BB~STEVE~BB STEVE IS A COOK I want to match 1st and 3rd... (2 Replies)
Discussion started by: crypto87
2 Replies

4. UNIX for Dummies Questions & Answers

using sed delete a line from csv file based on specific data in two separate fields

Hello, :wall: I have a 12 column csv file. I wish to delete the entire line if column 7 = hello and column 12 = goodbye. I have tried everything that I can find in all of my ref books. I know this does not work /^*,*,*,*,*,*,"hello",*,*,*,*,"goodbye"/d Any ideas? Thanks Please... (2 Replies)
Discussion started by: Chris Eagleson
2 Replies

5. Shell Programming and Scripting

Merging CSV fields based on a common field

Hi List, I have two files. File1 contains all of the data I require to be processed, and I need to add another field to this data by matching a common field in File2 and appending a corresponding field to the data in File1 based on the match... So: File 1:... (1 Reply)
Discussion started by: landossa
1 Replies

6. UNIX for Dummies Questions & Answers

compare two files based on common field in unix

I have two files in UNIX. 1st file is Entity and Second File is References. 1st File has only one column named Entity ID and 2nd file has two columns Entity ID | Person ID. I want to produce a output file where entity id's are matching in both the files. Entity File 624197 624252 624264... (4 Replies)
Discussion started by: PRS
4 Replies

7. Shell Programming and Scripting

extract data in a csv file based on a certain field.

I have a csv file that I need to extract some data from depending on another field after reading info from another text file. The text file would say have 592560 in it. The csv file may have some data like so Field 1 Field2 Field3 Field4 Field5 Field6 20009756 1 ... (9 Replies)
Discussion started by: GroveTuckey
9 Replies

8. Shell Programming and Scripting

join files based on a common field

Hi experts, Would you please help me with this? I have several files and I need to join the forth field of them based on the common first field. here's an example... first file: 280346 39.88 -75.08 547.8 280690 39.23 -74.83 538.7 280729 40.83 -75.08 499.2 280907 40.9 -74.4 507.8... (5 Replies)
Discussion started by: GoldenFire
5 Replies

9. Shell Programming and Scripting

Merging 2 files based on a common column

Hi All, I do have 2 files file 1 has 4 tab delimited columns 234 a c dfgyu 294 b g fih 302 c h jzh 328 z c san 597 f g son File 2 has 2 tab delimted columns 234 23 302 24 597 24 I want to merge file 2 with file 1 based on the data common in both files which is the first column so... (6 Replies)
Discussion started by: Lucky Ali
6 Replies

10. Shell Programming and Scripting

Matching lines across multiple csv files and merging a particular field

I have about 20 CSV's that all look like this: "","","","","","","","","","","","","","","",""What I've been told I need to produce is the exact same thing, but with each file now containing the start_code from every other file where the email matches. It doesn't matter if any of the other... (1 Reply)
Discussion started by: Demosthenes
1 Replies
Login or Register to Ask a Question