Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need Perl script to compare two CSV files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need Perl script to compare two CSV files
# 1  
Old 03-14-2011
Data Need Perl script to compare two CSV files
Need perl script to compare the two CSV files and and give out put in CSV format

File MsPMTP.csv
File ProfileNames.csv

MsPMTP.csv is having lines like below
Code:
JBL_VIJ_A_A962/r01sr4sl12/port#01-#13-Au4P-4c-TMi-PMNETR15
JBL_VIJ_A_A962/r01sr4sl12/port#01-#13-Au4P-4c-TMi-PMFETR15
JBL_VIJ_A_A962/r01sr4sl06/port#04-MsTTP-PMNETR15
JBL_VIJ_A_A962/r01sr4sl06/port#04-MsTTP-PMFETR15
JBL_VIJ_A_A962/r01sr4sl07/port#04-MsTTP-PMNETR15
JBL_VIJ_A_A962/r01sr4sl07/port#04-MsTTP-PMFETR15
JBL_VIJ_A_A962/r01sr4sl06/port#01-MsTTP-PMNETR15
JBL_VIJ_A_A962/r01sr4sl06/port#01-MsTTP-PMFETR15

ProfileNames.csv is having lines like below
Code:
Profile Name,PM TP
AESV-T4733,JBL_VIJ_A_A962/r01sr4sl12/port#01-#13-Au4P-4c-TMi-PMNETR15
AESV-T4733,JBL_VIJ_A_A962/r01sr4sl12/port#01-#13-Au4P-4c-TMi-PMFETR15 
AESV-T4733,JBL_VIJ_A_A962/r01sr4sl12/port#01-#13-Au4P-4c-TMe-PMNETR15    
AESV-T4733,JBL_VIJ_A_A962/r01sr4sl12/port#01-#13-Au4P-4c-TMe-PMFETR15
AESV-T4733,JBL_VIJ_A_A962/r01sr4sl13/port#02-#29-Au4P-4c-TMi-PMNETR15    
AESV-T4733,JBL_VIJ_A_A962/r01sr4sl13/port#02-#29-Au4P-4c-TMi-PMFETR15
AESV-T4733,JBL_VIJ_A_A962/r01sr4sl13/port#02-#29-Au4P-4c-TMe-PMNETR15
AESV-T4733,JBL_VIJ_A_A962/r01sr4sl13/port#02-#29-Au4P-4c-TMe-PMFETR15

If MsPMTP.csv line “T4733,JBL_VIJ_A_A962/r01sr4sl12/port#01-#13-Au4P-4c-TMi-PMNETR15
” is present in ProfileNames.csv it has to print in CSV file like

Profile Name,PM TP

Example : AESV-T4733,JBL_VIJ_A_A962/r01sr4sl12/port#01-#13-Au4P-4c-TMi-PMNETR15

If NOT it have to print

Example :No Profile, JBL_VIJ_A_A962/r01sr4sl06/port#04-MsTTP-PMNETR15

Both out put should be in same file

please some one help to resolve this issue

Moderator's Comments:
Mod Comment Removed copious amounts of formatting; added code tags
Last edited by Scott; 04-05-2011 at 02:44 PM..
# 2  
Old 03-14-2011
Could this help you?

Code:
#!/usr/bin/perl

open(LK,"mspmtp.csv") or die "$!\n";
open(DF,"ProfileNames.csv")  or die "$!\n";

while (<DF>) {
chomp;
@flds=split(/,/);
$lookup{substr($_,index($_,"_"))}=$flds[0];
}
close(DF);

while(<LK>){
chomp;
if (exists $lookup{substr($_,index($_,"_"))}) {
print $lookup{substr($_,index($_,"_"))},",",$_,"\n";
} else {
print "No Profile,",$_,"\n";
}
}
close(LK);

# 3  
Old 03-15-2011
thanks for quick support,
but it is printing only no profile out put like below
Code:
No Profile,JND_PTN_A_A838/r01sr1sl37/port#001-MsTTP-PMNETR15
No Profile,JND_PTN_A_A838/r01sr1sl37/port#001-MsTTP-PMFETR15
No Profile,MRG_BRD_A_A391/r01sr1sl33/port#004-MsTTP-PMFETR15
No Profile,MRG_BRD_A_A391/r01sr1sl33/port#004-MsTTP-PMNETR15
No Profile,MRG_BMR_A_A390/r01sr1sl13/port#003-MsTTP-PMNETR15
No Profile,MRG_BMR_A_A390/r01sr1sl13/port#003-MsTTP-PMFETR15
No Profile,CHD_BCL_A_A081/r01sr1sl25/port#001-MsTTP-PMFETR15
No Profile,KTN_MMN_A_A417/r01sr1sl28/port#001-MsTTP-PMFETR15
No Profile,KTN_MMN_A_A417/r01sr1sl28/port#001-MsTTP-PMNETR15
No Profile,BGL_BNR_A_A333/r01sr1sl25/port#001-MsTTP-PMNETR15
No Profile,BGL_BNR_A_A333/r01sr1sl25/port#001-MsTTP-PMFETR15
No Profile,BGL_BNR_A_A333/r01sr1sl37/port#001-MsTTP-PMNETR15
No Profile,BGL_BNR_A_A333/r01sr1sl37/port#001-MsTTP-PMFETR15
No Profile,BGL_BNR_A_A078/r01sr1sl24/port#001-MsTTP-PMNETR15
No Profile,BGL_BNR_A_A078/r01sr1sl24/port#001-MsTTP-PMFETR15
No Profile,PTN_BCM_A_A440/r01sr1sl25/port#001-MsTTP-PMNETR15
No Profile,PTN_BCM_A_A440/r01sr1sl25/port#001-MsTTP-PMFETR15

but i need
the pmtm which are present in profile.csv file that also it should print
Code:
PM_AB,BGL_BNR_A_A078/r01sr1sl27/port#002-MsTTP-PMFE1d
PM_AB,AMD_SAT_A_A996/r04sr3sl09/port#12-MsTTP-PMNE1d
PM_AB,AMD_SAT_A_A041/r01sr3sl09/port#16-MsTTP-PMNE1d
PM_AB,BLM_FOR_A_A1006/r01sr3sl12/port#05-MsTTP-PMNE1d
PM_AB,ATU_NAR_A_A167/r01sr1sl36/port#001-MsTTP-PMFE1d
PM_AB,BRS_NAD_A_A984/r07sr3sl15/port#03-MsTTP-PMFE1d
PM_AB,BRS_NAD_A_A984/r07sr3sl15/port#03-MsTTP-PMNE1d
PM_AB,BWR_PAT_A_A327/r01sr3sl14/port#10-MsTTP-PMFE1d
PM_AB,BWR_PAT_A_A327/r01sr3sl14/port#10-MsTTP-PMNE1d
PM_AB,BWR_PAT_A_A327/r01sr3sl14/port#11-MsTTP-PMFE1d
PM_AB,BWR_PAT_A_A327/r01sr3sl14/port#11-MsTTP-PMNE1d
PM_AB,BWR_PAT_A_A327/r01sr3sl15/port#10-MsTTP-PMFE1d
PM_AB,BWR_PAT_A_A327/r01sr3sl15/port#10-MsTTP-PMNE1d
PM_AB,BWR_PAT_A_A327/r01sr3sl15/port#11-MsTTP-PMFE1d
PM_AB,BWR_PAT_A_A327/r01sr3sl15/port#11-MsTTP-PMNE1d

Last edited by Scott; 04-05-2011 at 02:36 PM..
# 4  
Old 03-15-2011
Please post your sample input files. Its working fine at my end with your given data in post #1.
# 5  
Old 03-15-2011
attached the input files
please have look
thanks...
Input_files.zip (607.1 KB) 
# 6  
Old 03-15-2011
Quote:
Originally Posted by pravin27
Could this help you?

Code:
#!/usr/bin/perl

open(LK,"mspmtp.csv") or die "$!\n";
open(DF,"ProfileNames.csv")  or die "$!\n";

while (<DF>) {
chomp;
@flds=split(/,/);
$lookup{substr($_,index($_,"_"))}=$flds[0];
}
close(DF);

while(<LK>){
chomp;
if (exists $lookup{substr($_,index($_,"_"))}) {
print $lookup{substr($_,index($_,"_"))},",",$_,"\n";
} else {
print "No Profile,",$_,"\n";
}
}
close(LK);


just swap the file handlers , see bold lines above make them as below.

Code:
open(DF,"mspmtp.csv") or die "$!\n";
open(LK,"ProfileNames.csv")  or die "$!\n";

# 7  
Old 03-16-2011
OUTput
Code:
No Profile Profile Name PM TP
No Profile AESV-T4733 HYD_SPT_A_A587/r01sr3sl06/port#07-#01-Au4P-4c-TMe-PMFETR15
No Profile AESV-T4733 HYD_SPT_A_A587/r01sr3sl06/port#07-#01-Au4P-4c-TMe-PMNETR15
No Profile AESV-T4733 HYD_SPT_A_A587/r01sr3sl06/port#07-#01-Au4P-4c-TMi-PMFETR15
No Profile AESV-T4733 HYD_SPT_A_A587/r01sr3sl06/port#07-#01-Au4P-4c-TMi-PMNETR15
No Profile AESV-T4733 HYD_SPT_A_A587/r01sr3sl13/port#03-#05-Au4P-4c-TMe-PMFETR15
No Profile AESV-T4733 HYD_SPT_A_A587/r01sr3sl13/port#03-#05-Au4P-4c-TMe-PMNETR15
No Profile AESV-T4733 HYD_SPT_A_A587/r01sr3sl13/port#03-#05-Au4P-4c-TMi-PMFETR15
No Profile AESV-T4733 HYD_SPT_A_A587/r01sr3sl13/port#03-#05-Au4P-4c-TMi-PMNETR15
No Profile AESV-T4733 EBD_BHA_A_A011/r01sr3sl02/port#03-#05-Au4P-4c-TMi-PMNETR15
No Profile AESV-T4733 EBD_BHA_A_A011/r01sr3sl02/port#03-#05-Au4P-4c-TMi-PMFETR15
No Profile AESV-T4733 EBD_BHA_A_A011/r01sr3sl02/port#03-#05-Au4P-4c-TMe-PMNETR15
No Profile AESV-T4733 EBD_BHA_A_A011/r01sr3sl02/port#03-#05-Au4P-4c-TMe-PMFETR15
No Profile AESV-T4733 EBD_BHA_A_A011/r01sr3sl03/port#03-#05-Au4P-4c-TMi-PMNETR15
No Profile AESV-T4733 EBD_BHA_A_A011/r01sr3sl03/port#03-#05-Au4P-4c-TMi-PMFETR15

this is giving for PMTps to no profile even the profile present "AESV-T4733"

please help me to reslove this

---------- Post updated 03-16-11 at 11:06 AM ---------- Previous update was 03-15-11 at 05:28 PM ----------

Quote:
Originally Posted by sreedhargouda
please have look
thanks...
Last edited by Scott; 04-05-2011 at 02:35 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Example: I have files in below format file 1: zxc,133,joe@example.com cst,222,xyz@example1.com File 2 Contains: hxd hcd jws zxc cst File 1 has 50000 lines and file 2 has around 30000 lines : Expected Output has to be : hxd hcd jws (5 Replies)
Discussion started by: TestPractice
5 Replies

2. Shell Programming and Scripting

Script to compare count of two csv files

Hi Guys, I need to write a script to compare the count of two csv files each having 5 columns. Everyday a csv file is recived. Now we need to compare the count of todays csv file with yesterday's csv file and if the total count of records is same in todays csv file and yesterday csv file out... (3 Replies)
Discussion started by: Vivekit82
3 Replies

3. Shell Programming and Scripting

Perl script to Convert XLSX or XLS files to CSV file

Hi All, I've got in a situation where I need to convert .xlsx or .xls formatted files into simple text file or .csv file. I've found many options but doing this using PERL script is the best way I believe.I'm in AIX box. Perl code should have 2 params while running. i.e perl... (1 Reply)
Discussion started by: manab86
1 Replies

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

5. Shell Programming and Scripting

Perl script to compare two files

hi, As such I am new to perl on google search I found a code for Perl script to compare two files and print differences between them and instead of prinintg I want to store the diff. in a outputfile so can sombody provide assistance upon this from where can I edit in script to store the diff in... (1 Reply)
Discussion started by: dinesh.4126
1 Replies

6. Shell Programming and Scripting

Help with script to open and compare csv files

We are testing an application that accesses two tables: A and B. I am to write a script to validate the ouput files of this application.The application marks any account that has become overdue as per rule. When it runs, it updates the overdue flag in the A table according to the following rules: ... (1 Reply)
Discussion started by: inkyponky
1 Replies

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

8. UNIX for Dummies Questions & Answers

How to compare csv files using perl

I need to compare 2 csv files and report should containg number of matching lines,different lines ,missing lines in one file using perl. I dont want to use read line by line and scan thru the second file for matching line ,as this logic was so time consuming .Can other ideas .please respond asap... (2 Replies)
Discussion started by: kittu1979
2 Replies

9. UNIX for Dummies Questions & Answers

Compare 2 csv files in perl

need to compare 2 csv files and report should containg number of matching lines,different lines ,missing lines in one file using perl. I dont want to use read line by line and scan thru the second file for matching line ,as this logic was so time consuming .Any ideas.i need the soultion badly .... (2 Replies)
Discussion started by: kittu1979
2 Replies

10. Shell Programming and Scripting

compare 2 coloum of 2 diff files using perl script

Hi, i am new to perl scripting.. i am still learing it.. i am asked to write a perl script which should compare 2 coloums of 2 different files. if those 2 coloumn are same the script should store the both the lines in 2 diff files. these are files, file 1: 21767016 226112 char 19136520... (3 Replies)
Discussion started by: vasuki
3 Replies
Login or Register to Ask a Question