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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to compare two csv files values and write into another csv file
# 1  
Old 02-06-2009
Data 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 the second coulmnof 1.csv value with 2.csv . if it is more than the 2.csv value then print 1.csv values and second coulmn value of 2.csv and like it goes..And remember the second column of 1.csv is a float value;

the ouput should be in 3.csv

def,2.13,1


my code is goes as folows:

1st way :

for line in `cat 1.csv`
do
samp1=`echo $line|cut -d\'' -f1`;
samp2=`echo $line|cut -d\'' -f2`;
for line in `cat 2.csv`
do
samp3=`echo $line|cut -d\'' -f1`;
samp4=`echo $line|cut -d\'' -f2`;
if [ $samp1 -eq $samp3 ]
then
if [ $samp2 > $samp4 ]
then
print "$samp1",""$samp2"",""$samp4">> 3.csv;
break;
fi
fi
done
done

--------------------------------

2nd way

Using Awk i tried :

awk -F"," 'BEGIN { {samp1=$1 ; samp2=$2} {awk 'BEGIN {FS = ","} NR == 1 {samp3=$1 ; samp4=$2} \
{if [ $samp1 -eq $samp3 ]
then
if [ $samp2 -gt $samp4 ]
then
print "$samp1",""$samp2"",""$samp3" >> 3.csv
break
fi
fi}}}'1.csv}'2.csv

Using awk in awk...am not sure..Smilie can anybody help on these two ways...am really worried about this since yesterday...Smilie


ManyThanks,
Chinna..Smilie
# 2  
Old 02-06-2009
Perhaps have a look at diff.
# 3  
Old 02-06-2009
MySQL

Hi Chinna,

Oops...correct me if i m wrong...

let me confirm once again..the result that you want..

file1.csv file2.csv
abc,2 abc,1
def,3 def,2


paste -d , file1.csv file2.csv > file3.csv

...file3.csv
abc,2,1
def,3,2

..here is the small solution:

awk -F"," '{if($2 > $4) {print $0}}' file3.csv|awk -F"," '{if($1 == $3) {print $1","$2","$4}}'

Output! will be like below:

abc,2,1



Thanks
Sha
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare every column from one csv file to another csv file

1.csv contains following column- Empid code loc port 101 A xy 01 102 B zx 78 103 A cg 12 104 G xy 78 2.csv contains follwing data- Empid code loc port 101 A gf 01 102 B zx 78 103 C cg 32 104 ... (1 Reply)
Discussion started by: rishabh
1 Replies

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

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

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

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

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

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

8. Shell Programming and Scripting

Match list of strings in File A and compare with File B, C and write to a output file in CSV format

Hi Friends, I'm a great fan of this forum... it has helped me tone my skills in shell scripting. I have a challenge here, which I'm sure you guys would help me in achieving... File A has a list of job ids and I need to compare this with the File B (*.log) and File C (extend *.log) and copy... (6 Replies)
Discussion started by: asnandhakumar
6 Replies

9. Shell Programming and Scripting

Need to compare values on two CSV files

:( Hello, Having a problem with reading two files using awk/nawk, am new to both them. I need to compare field values between two csv files and arrange for an appropriate output if both the values are equal or not for each feild. $cat File1.csv... (4 Replies)
Discussion started by: pgop
4 Replies

10. Shell Programming and Scripting

Compare two csv files by two colums and create third file combining data from them.

I've got two large csv text table files with different number of columns each. I have to compare them based on first two columns and create resulting file that would in case of matched first two columns include all values from first one and all values (except first two colums) from second one. I... (5 Replies)
Discussion started by: agb2008
5 Replies
Login or Register to Ask a Question

Featured Tech Videos