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
# 8  
Old 11-27-2014
There is no 6. field in file2, so your assignment k[$2] = $6 will assign an empty value to the k array. Adapting Don Cragun's proposal according to your new spec would result in
Code:
awk     'BEGIN          {FS = OFS = ","}
         FNR == NR      {k[$2] = $1; next}
         $6 in k        {$13 = 1; $14 = k[$6]}
         1
        ' file2 file1

, but that would not modify your files from post#1 as there is no 6. field in file1 matching the 2. field in file2.
# 9  
Old 11-27-2014
Hi Scrutinizer,

here is the new samples:

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,00006298H1,0,\N,\N,\N,\N,1,00.30887539,0.00050312

Code:
file2:
red,00020460A1
orange,00006298G1
blue,00020460B1
yelloy,00020460C1


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


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

# 10  
Old 11-27-2014
Hi djoseph, did you try my suggestion in post #7? It does seem to produce the desired output with the new samples..
# 11  
Old 11-27-2014
Tnx u for the quick reply & help - Scrutinizer & RudiC...
Both(post#7 & post#8) solution's work perfectly for my task Smilie
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