Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Matching lines across multiple csv files and merging a particular field

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Matching lines across multiple csv files and merging a particular field
# 1  
Old 07-29-2010
Matching lines across multiple csv files and merging a particular field
I have about 20 CSV's that all look like this:

Code:
"[email]","[fname]","[lname]","[prefix]","[suffix]","[fax]","[phone]","[business]","[address1]","[address2]","[city]","[state]","[zip]","[setdate]","[email_type]","[start_code]"

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 fields don't match, just the email field is important, and the only change to each file would be to add any other start_code values from other files where the email matches.

For example, if the same email appeared in the wicq.csv, oota.csv, and itos.csv it would go from being the following in each file:
Code:
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"WIQC PDX"
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"OOTA"
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"ITOS"

to

Code:
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"WIQC PDX, OOTA, ITOS"

for all three files (wicq.csv, oota.csv, and itos.csv)

I've done some googling, but haven't found anything yet. Any help is appreciated, even if it's just what the proper terms I should be searching for are.
# 2  
Old 07-29-2010
Hi,

if you have three files file1, file2 and file3 with content like:

file1:
Code:
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"WIQC PDX"
"berti@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"ALBI QUAZ"

file2:
Code:
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"OOTA"
"berti@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"TUZZI"

file3:
Code:
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"ITOS"
"berti@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,"PANZ"

Code:
awk -F, '{if (! a[$1]){for (i=1;i<NF;i++){s=s $i FS}a[$1]=s FS $NF;s=""} \
    else{a[$1]=a[$1] $NF}}\
    END{for (i in a){gsub(/""/,", ",a[i]); print a[i]}}' file1 file2 file3

You will get:
Code:
"berti@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,,"ALBI QUAZ, TUZZI, PANZ"
"anon@yahoo.com","anon",,,,,,,,,,,,01/16/08 08:05 PM,,,"WIQC PDX, OOTA, ITOS"

HTH Chris
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merging multiple lines to columns with awk, while inserting commas for missing lines

Hello all, I have a large csv file where there are four types of rows I need to merge into one row per person, where there is a column for each possible code / type of row, even if that code/row isn't there for that person. In the csv, a person may be listed from one to four times... (9 Replies)
Discussion started by: RalphNY
9 Replies

2. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

3. Shell Programming and Scripting

Matching the header of a .CSV file with dynamic field names

I have a .CSV file (frequency - weekly) whose header contains the year-week value in two of the columns which keeps changing every week. For an instance please see below. Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,201420... (4 Replies)
Discussion started by: dhruuv369
4 Replies

4. Linux

How do I format a Date field of a .CSV file with multiple commas in a string field?

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below: column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10 "12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

5. UNIX for Dummies Questions & Answers

Need help combining txt files w/ multiple lines into csv single cell - also need data merge

:confused:Hello -- i just joined the forums. I am a complete noob -- only about 1 week into learning how to program anything... and starting with linux. I am working in Linux terminal. I have a folder with a bunch of txt files. Each file has several lines of html code. I want to combine... (2 Replies)
Discussion started by: jetsetter
2 Replies

6. Shell Programming and Scripting

Merging multiple files using lines from one file

I have been working of this script for a very long time and I have searched the internet for direction but I am stuck here. I have about 3000 files with two columns each. The length of each file is 50000. Each of these files is named this way b.4, b.5, b.6, b.7, b.8, b.9, b.10, b.11, b.12... (10 Replies)
Discussion started by: iconig
10 Replies

7. Shell Programming and Scripting

Help with merging two CSV files

Hi, I have following 2 CSV files file1.txt A1,B1,C1,D1,E1 A2,B2,C2,D2,E2 A3,B3,C3,D3,E3 .... file2.txt A1,B1,P1,Q1,R1,S1,T1,U1 A1,B1,P2,Q2,R2,S2,T2,U2 A1,B1,P3,Q3,R3,S3,T3,U3 A2,B2,X1,Y1,Z1,I1,J1,K1 A2,B2,X2,Y2,Z2,I2,J2,K2 A2,B2,X3,Y3,Z3,I3,J3,K3 A2,B2,X4,Y4,Z4,I4,J4,K4... (2 Replies)
Discussion started by: learnoutmore99
2 Replies

8. 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

9. 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

10. Shell Programming and Scripting

Field validations in multiple files CSV

Hi, I am regular reader of this forum. My advanced thanks to everyone. Below given are the sample files INDATA (Main data) Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. . . N records (140000) eg GRPDATA (Reference file) (2 Replies)
Discussion started by: hyperion.krish
2 Replies
Login or Register to Ask a Question