Merging CSV fields based on a common field


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merging CSV fields based on a common field
# 1  
Old 02-09-2012
Java 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:
Code:
dbid,uuid,account_code,hostname,os,Support_group_name,location
123,aa79-11df-829d,BMC,bnehws2,Microsoft Windows,Server Ops,UNKNOWN
345,9b7a-11e0-9810,AAA,psmpd01o,Microsoft Windows,UNKNOWN,UNKNOWN
789,fe63-11df-98bd,CCS,hnam01,Red Hat Linux,unix support,Sydney
124,072c-11e0-8171,CCS,syd71,Microsoft Windows,Windows support,INDIA

I want to match the account_code field with the Org Code in File 2:
Code:
Org Code,Account Name,Org ID,IntID,Region,Status,Comments
BMC,BMC Corp,111,752654929,AUS,Active,
AAA,AAA Corp,104,750335008,AUS,Active,
CCS,CCS Corp,185,758011030,EMEA,Active,
DDD,DDD Corp,992,756512445,EMEA,Active,

And then merge the matched Account Name(field 2) into the data in File1 without omitting any records in File1, so output data looks like this:
Code:
dbid,uuid,account_code,hostname,os,Support_group_name,location
123,aa79-11df-829d,BMC,bnehws2,Microsoft Windows,Server Ops,UNKNOWN,BMC Corp
345,9b7a-11e0-9810,AAA,psmpd01o,Microsoft Windows,UNKNOWN,UNKNOWN,AAA Corp
789,fe63-11df-98bd,CCS,hnam01,Red Hat Linux,unix support,Sydney,CCS Corp
124,072c-11e0-8171,CCS,syd71,Microsoft Windows,Windows support,INDIA,CCS Corp

Any suggestions much appreciated..

Thanks,
Land

Last edited by Franklin52; 02-09-2012 at 04:17 AM.. Reason: Please use code tags for code and data samples, thank you
# 2  
Old 02-09-2012
Code:
perl -F, -ane '($.==1)&&print;
chomp ($line = $_); open I, "< file2";
for (<I>) {
    @x = split /,/;
    if ($F[2] eq $x[0]) {
        print "$line,$x[1]\n";
    }
}' file1


Last edited by balajesuri; 02-09-2012 at 04:16 AM..
This User Gave Thanks to balajesuri For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to print lines based on text in field and value in two additional fields

In the awk below I am trying to print the entire line, along with the header row, if $2 is SNV or MNV or INDEL. If that condition is met or is true, and $3 is less than or equal to 0.05, then in $7 the sub pattern :GMAF= is found and the value after the = sign is checked. If that value is less than... (0 Replies)
Discussion started by: cmccabe
0 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

Read in 2-column CSV, output many files based on field

Is there a way to read in a two-columned CSV file, and based on the fields in 1st column, output many different files? The input/output looks something like: input.csv: call Call Mom. call Call T-Mobile. go Go home. go Go to school. go Go to gas station. play Play music. play Play... (4 Replies)
Discussion started by: pxalpine
4 Replies

4. Shell Programming and Scripting

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: date,hostname,status,color,check 02-03-2012,COMP1,FAIL,Yellow,auth_pass_change... (3 Replies)
Discussion started by: landossa
3 Replies

5. UNIX for Dummies Questions & Answers

Please help me to find out maximum value of a field based on grouping of other fields.

Please help me to find out maximum value of a field based on grouping of other fields, as we do in SQL. Like in SQL if we are having below records : Client_Name Associate_Name Date1 Value C1111 A1111 2012-01-17 10 C1111 A1111 ... (1 Reply)
Discussion started by: KamalKumarKalra
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