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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare 2 csv files in ksh and o/p the difference in a new csv file
# 1  
Old 03-14-2013
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 expected o/p is a new csv file say - file3.csv should contain the details of IDs which are present in both the files but with some of the data related to it being different (here version and cost) - as shown below.



Expected o/p file - file3.csv

ID,field,old,new
2000,cost,40,45
3000,version,3,4
3000,cost,50,55
I need a unix ksh script for doing this
. Please help me out.

Last edited by Naresh101; 03-14-2013 at 02:04 AM..
# 2  
Old 03-14-2013
Can you kindly explain what difference this question has with the one in posted this thread ?
# 3  
Old 03-14-2013
Check the solution posted in your previous thread
https://www.unix.com/shell-programmin...-csv-file.html
# 4  
Old 03-14-2013
Also why are you so particular about a solution using bash or ksh!

Is this a homework question?
# 5  
Old 03-14-2013
Its a small part in my intern project.... I m quiet new to unix shell scripting... so need the help...
# 6  
Old 03-14-2013
Here is a bash script:
Code:
#!/bin/bash

while IFS="," read f1_ID f1_VER f1_COST
do
        [[ "$f1_ID" =~ ID ]] && continue;

        while IFS="," read f2_ID f2_VER f2_COST
        do
                [[ "$f2_ID" =~ ID ]] && continue;

                if [ $f1_ID -eq $f2_ID ]
                then
                        if [ $f1_ID -eq $f2_ID ] && [ $f1_VER -eq $f2_VER ] && [ $f1_COST -eq $f2_COST ]
                        then
                                continue;
                        fi
                        [[ $f1_VER -ne $f2_VER ]] && printf "${f1_ID},version,${f1_VER},${f2_VER}\n"
                        [[ $f1_COST -ne $f2_COST ]] && printf "${f1_ID},cost,${f1_COST},${f2_COST}\n"
                fi
        done < file2.csv
done < file1.csv

This User Gave Thanks to Yoda For This Post:
# 7  
Old 03-15-2013
Thank you @Yoda....
But I used the segment with awk in your other post in my code as below ... That worked fine too.. Thanks a lot

Code:
join -t"," -1 1 -2 1 -a1 file1.csv file2.csv | awk -F, ' BEGIN {
         print "ID,field,old,new"
 } NF > 3 { 
        if ( $3 != $5 )   
              print $1, "cost", $3, $5        
       if ( $2 != $4 )              
            print $1, "version", $2, $4 } ' OFS=, 

echo "the IDs which are not present in first file are ">>file3000.csv 
awk -F, 'NR==FNR{_1[$1]++;next}!_1[$1]' file81.csv file82.csv >> file3000.csv 
cut -d',' -f1 file3000.csv>>file3001.csv 
cat file3001.csv  

echo "the IDs which are not present in second file are ">>file4000.csv 
awk -F, 'NR==FNR{_1[$1]++;next}!_1[$1]' file82.csv 
file81.csv >> file4000.csv cut -d',' -f1 file4000.csv>>file4001.csv 
cat file4001.csv

In addition to list down the differences in fields(cost / version) between 2 files I also needed to create a new file
which contains the missing IDs in a file compared to the other... The o/p which I got is as below...

ID,field,old,new
2000,cost,40,45
3000,version,3,4
3000,cost,50,55
the IDs which are not present in first file are
6000
7000
the IDs which are not present in second file are
4000
8000

---------- Post updated at 04:39 PM ---------- Previous update was at 04:24 PM ----------

And can I attain the same result as mentioned above using perl scripting?..
In that case what changes should I make?... I went through Perl script basics but it seemed to be different altogether from bash/ksh...

Last edited by Naresh101; 03-15-2013 at 08:05 AM..
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

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

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

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

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

6. Shell Programming and Scripting

Difference between two huge .csv files

Hi all, I need help on getting difference between 2 .csv files. I have 2 large . csv files which has equal number of columns. I nned to compare them and get output in new file which will have difference olny. E.g. File1.csv Name, Date, age,number Sakshi, 16-12-2011, 22, 56 Akash,... (10 Replies)
Discussion started by: Dimple
10 Replies

7. Shell Programming and Scripting

Comparing 2 difference csv files

Hello, I have about 10 csv files which range from csv1 - csv10. Each csv file has same type/set of tabs and we have around 5-6 tabs for each of the csv file which have slightly different content(data). A sample of CSV1 is shown below: Joins: Data related to Joins, it can be any number of... (2 Replies)
Discussion started by: bobby1015
2 Replies

8. Shell Programming and Scripting

Counting difference in two CSV files

Hi, I am new to awk and trying to count the difference between the first columns of two CSV files. -------- Sample input (header is:name, id1,id2): file1.csv name, id1,id2 sss,34,56 yyy,3,56 www,56,78 pppp,43,12 file2.csv name,id1,id2 sss,32,56 yyy,12,7 ttt,4,8 uuu,7,9 (0 Replies)
Discussion started by: sam40
0 Replies

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

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

Featured Tech Videos