Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Match columns from two csv files and update field in one of the csv file
# 1  
Old 11-26-2014
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:

Code:
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
201,LES_POUGES_ASF,\N,201,201,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312


The second file has unique rows and fields(particularly field 2):

Code:
file2:
000116T3,1AA
000117T3,888
000118T3,201
000122T3,2BB


What I am trying to achieve is :-
Code:
if (12th field value in file1='1')
	then 
		if  ( match the 1st field value of the first file with 2nd field value of the 2nd file)
			then replace 6th field value of file1 with 1st field value of the matching row of the 2nd file 
		 else
			do nothing
	else do nothing


Code:
Sample required output:
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,000122T3,0,\N,\N,\N,\N,1,00.30887539,0.00050312
202,LES_POUGES_ASF,\N,201,201,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312


Does anyone know of any solution?

I tried to modify a solution which I found in a similar thread named "Matching and Merging csv data fields based on a common field" but in vain Smilie

Thanks in advance
Moderator's Comments:
Mod Comment Please use CODE tags (not ICODE tags) for multi-line input, output, and code samples.
Last edited by Don Cragun; 11-26-2014 at 01:58 AM.. Reason: Change ICODE tags to CODE tags for multi-line data.
# 2  
Old 11-26-2014
The following awk script seems to do what you said you want:
Code:
awk '
BEGIN {	FS = OFS = ","
}
FNR == NR {
	k[$2] = $1
	next
}
{	if($12 == 1 && $1 in k) $6 = k[$1]
}
1' file2 file1

But, with your sample input files it produces the output:
Code:
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,000122T3,0,\N,\N,\N,\N,1,00.30887539,0.00050312
201,LES_POUGES_ASF,\N,201,201,000118T3,0,\N,\N,\N,\N,1,00.30887539,0.00050312

which disagrees with the output you said you wanted on the 3rd line. (Nothing in your description talked about changing the contents of the 1st field from "201" to "202" under any circumstances???)

You didn't say what OS or shell you're using. If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk.
Last edited by Don Cragun; 11-26-2014 at 05:52 AM.. Reason: Fix mismatched ".
This User Gave Thanks to Don Cragun For This Post:
djoseph
# 3  
Old 11-26-2014
You will find are many examples of this kind of join operation.. Try:
Code:
awk 'NR==FNR{A[$2]=$1; next} $12==1 && $1 in A{$6=A[$1]}1' FS=, OFS=, file2 file1

--edit--
Mornin' Don Smilie
This User Gave Thanks to Scrutinizer For This Post:
djoseph
# 4  
Old 11-26-2014
Many thanks to Don Cragun and Scrutinizer.

Both solution's work perfectly for my task.

My apologies for the 3rd line. It was a typo error. Please ignore.
# 5  
Old 11-26-2014
Love them one liners. Smilie

Code:
#!/bin/bash

while read f2
do
    kee2=${f2##*,}
    line=$(grep $kee2 file1)
    if [[ $line && $(echo $line | cut -d, -f12) == 1 ]]; then
        srch=$(echo $line | cut -d, -f6)
        repl=${f2%,*}
        linenum=$(sed -n "/$kee2/=" file1)
        sed -i "$linenum s/$srch/$repl/g" file1
    fi
done < file2
cat file1

output
------
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,000122T3,0,\N,\N,\N,\N,1,00.30887539,0.00050312
201,LES_POUGES_ASF,\N,201,201,000118T3,0,\N,\N,\N,\N,1,00.30887539,0.00050312

This User Gave Thanks to ongoto For This Post:
spr89
# 6  
Old 11-27-2014
Hi Don Cragun...Need your help again...
I'm trying to modify the script you provided to achieve a similar objective but the script wouldn't work.

I would want the modified script to search sixth field in file1 with 2nd field value of file2 i.e.

if ( match the 6th field value of the first file with 2nd field value of the 2nd file)
assign file1 $13=1 and $14=1st field value of the second file
else do nothing

my modified script looks like this:
Code:
#!/bin/bash

awk '
BEGIN { FS = OFS = ","
}
FNR == NR {
        k[$2] = $6
        next
}
{       if($6 in k) $13 =1;$14=k[$1]
}
1' file2 file1

# 7  
Old 11-27-2014
Something like this?
Code:
awk 'NR==FNR{A[$2]=$1; next} $6 in A{$13=1; $14=A[$6]}1' FS=, OFS=, file2 file1

Otherwise post new samples..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

UNIX Command to Match columns from two csv files

I am joining two CSV files based on 'Server_Name' column, 1st column of first file and 2nd column of second file. If matches, output 1st and 2nd column from first file, 3rd,4th,5th,6th columns from second file. I am expecting output CSV file as below. Could you please send me help me with... (6 Replies)
Discussion started by: Anadmbt
6 Replies

2. Shell Programming and Scripting

Matching two fields in two csv files, create new file and append match

I am trying to parse two csv files and make a match in one column then print the entire file to a new file and append an additional column that gives description from the match to the new file. If a match is not made, I would like to add "NA" to the end of the file Command that Ive been using... (6 Replies)
Discussion started by: dis0wned
6 Replies

3. Shell Programming and Scripting

Import 2 columns from 8 .csv files into pandas df (side by side) and write a new csv

I have 8 .csv files with 16 columns and "n" rows with no Header. I want to parse each of these .csv and get column and put the data into a new.csv. Once this is done, the new.csv should have 16 columns (2 from each input.csv) and "n" rows. Now, I want to just take the average of Column from... (3 Replies)
Discussion started by: Zam_1234
3 Replies

4. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

5. Shell Programming and Scripting

Update field value on a csv file

Hi I have a job status csv file. I want to update the status of the job in the file. Below is the csv file 1,jobname1,in_progress,starttime,somthing,somthing 2,jobname2,completed,starttime,somthing,somthing 3,jobname3,failed,starttime,somthing,somthing... (8 Replies)
Discussion started by: midhun19
8 Replies

6. Shell Programming and Scripting

Compare 2 csv files in ksh and o/p the difference in a new csv file

(say) I have 2 csv files - file1.csv & file2.csv as mentioned below: file1.csv ID,version,cost 1000,1,30 2000,2,40 3000,3,50 4000,4,60 file2.csv ID,version,cost 1000,1,30 2000,2,45 3000,4,55 6000,5,70 The... (7 Replies)
Discussion started by: Naresh101
7 Replies

7. Shell Programming and Scripting

Comparing 2 CSV files and sending the difference to a new csv file

(say) I have 2 csv files - file1.csv & file2.csv as mentioned below: file1.csv ID,version,cost 1000,1,30 2000,2,40 3000,3,50 4000,4,60 file2.csv ID,version,cost 1000,1,30 2000,2,45 3000,4,55 6000,5,70 ... (1 Reply)
Discussion started by: Naresh101
1 Replies

8. Shell Programming and Scripting

Compare two CSV files and put the difference in third file with line no,field no and diff value.

I am having two csv files i need to compare these files and the output file should have the information of the differences at the field level. For Example, File 1: A,B,C,D,E,F 1,2,3,4,5,6 File 2: A,C,B,D,E,F 1,2,4,5,5,6 out put file: (12 Replies)
Discussion started by: karingulanagara
12 Replies

9. UNIX for Advanced & Expert Users

Unix Bash: substitute columns in .csv using other .csv columns

Hi All, I have two .csv's input.csv having values as (7 columns) ABC,A19907103,ABC DEV YUNG,2.17,1000,2157,07/07/2006 XYZ,H00213850,MM TRUP HILL,38.38,580,23308,31/08/2010 output.csv having (25 columns) A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y... (4 Replies)
Discussion started by: abhivyas
4 Replies

10. Shell Programming and Scripting

Need to compare two csv files values and write into another csv file

Hi all, Am new to scripting. So i just need your ideas to help me out. Here goes my requirement. I have two csv files 1.csv 2.csv abc,1.24 abc,1 def,2.13 def,1 I need to compare the first column of 1.csv with 2.csv and if matches then need to compare... (2 Replies)
Discussion started by: chinnahyd
2 Replies
Login or Register to Ask a Question