csv file comparison with normal file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting csv file comparison with normal file
# 1  
Old 02-14-2012
Question csv file comparison with normal file

i will like to give my files as

fileB :
Code:
 
 
02.09.2011:MOU04/KUBIS--R_Lieferung_1/KUBIS_V15.0/vpn_wls_15-0-0b01
02.09.2011:MOU04/KUBIS--R_Lieferung_2/KUBIS_V15.0/apng_wls_15-0-0b02
31  02.09.2011:MOU04/KUBIS--R_Lieferung_2/KUBIS_V15.0/ecc_wls_15-0-0b02
32  02.11.2010:PPMOU/VOMS--VOMS-ASR3244/VoMS2.3.0_LieferungZu_AO-Order004385
33  02.11.2010:PPMOU/PRMKONF-RBO-01.00.13.00/XXX_mandatory_LieferungZu_AO-Order004513
33  02.11.2010:PROD/PRMKONF-RBO-01.00.13.00/XXX_mandatory_LieferungZu_AO-Order004513
34  02.11.2011:WOMS2/KUBIS--S_Lieferung_1/KUBIS_V16.0/kubis_db_16-0-0b01
02.11.2011:WOMS2/KUBIS--S_Lieferung_2/KUBIS_V16.0/kubis_db_16-0-0b02
36  02.11.2011:WOMS2/KUBIS--TS-InTime-VV/Lieferung_WoM_Maxx_VV_01_20111026
37  02.11.2011:WOMS2/PA--PA-MaxX-Konf/PA-MaxX-Konf-01.00.00.00
38  02.11.2011:WOMS2/PM--PM-5.0/PRM-LIB-05.00.00.00
39  02.11.2011:WOMS2/PM--PM-MaxX-Konf/PRMKONF-MaxX-01.00.00.00
40  02.11.2011:WOMS2/SALES--SK-Tools-2.0.0_Lieferung_01_20111024/SK-Tools-2.0/database-dist_2-0-0b01
41  02.11.2011:WOMS2/SALES--SK-Tools-2.0.0_Lieferung_01_20111024/SK-Tools-2.0/salescomponent-dist_2-0-0b01
42  02.12.2010:PPMOU/VOMS--AO-intern_RU10_VOMS_ASR3605/XXX_mandatory_LieferungZu_AO-Order004638

this is not a csv file or it does not contain any seperator. its just a file.

and my fileD is as follows
Code:
PRMKONF-RBO-01.00.13.00          PROD
REL_001.630.008_LieferungZu_AO-Order004266      PROD
CASS-CR099-1-2T         PPMOU
CASS-CR099-1-2T         PROD

this is a csv file . so now i want to compare this file first value of fileD ie "PRMKONF-RBO-01.00.13.00" in fileB. now if it is found in fileB whole line is copied and checked for second column of "PRMKONF-RBO-01.00.13.00" if this second column is present in that line or not. ie second column is PROD . so if this PROD is present in that line than it is not redirrected for output. if this is not present in that file then that row from fileD is redirected for output.

so output shall be
Code:
REL_001.630.008_LieferungZu_AO-Order004266      PROD
CASS-CR099-1-2T         PPMOU
CASS-CR099-1-2T         PROD

this PRMKONF-RBO-01.00.13.00 PROD is not outputed as "PRMKONF-RBO-01.00.13.00" is present and that line contains "PROD".
and also PRMKONF-RBO-01.00.13.00 can be present in many lines of fileB and each line should be considered for checking second columns of fileD.as for above example
"PRMKONF-RBO-01.00.13.00" was present in two line as

33 02.11.2010:PPMOU/PRMKONF-RBO-01.00.13.00/XXX_mandatory_LieferungZu_AO-Order004513
33 02.11.2010:PROD/PRMKONF-RBO-01.00.13.00/XXX_mandatory_LieferungZu_AO-Order004513

so PROD will be found in any of this line.

records are present in thousands..
many thanks in advance..
# 2  
Old 02-14-2012
MySQL

Please try with below script

Code:
#! /bin/bash
while read line
do
in1=`echo $line | awk '{print $1}'`
in2=`echo $line | awk '{print $2}'`
ou1=`grep $in1 fileB | grep $in2`
if [ -z "$ou1" ];then
echo $line
fi
done < fileD

When i execute it, below is the output
Quote:
REL_001.630.008_LieferungZu_AO-Order004266 PROD
CASS-CR099-1-2T PPMOU
CASS-CR099-1-2T PROD
Thanks,
Kalai
This User Gave Thanks to kalpeer For This Post:
# 3  
Old 02-15-2012
Quote:
Originally Posted by kalpeer
Please try with below script

Code:
#! /bin/bash
while read line
do
in1=`echo $line | awk '{print $1}'`
in2=`echo $line | awk '{print $2}'`
ou1=`grep $in1 fileB | grep $in2`
if [ -z "$ou1" ];then
echo $line
fi
done < fileD

When i execute it, below is the output
Thanks,
Kalai

thanks kalpeer . it is working...Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

9 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

Save output of updated csv file as csv file itself, part 2

Hi, I have another problem. I want to sort another csv file by the first field. result.csv SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw /home/intannf/foto5/2015_0313_090651_219.JPG,0.,-7.77223,110.37310,30.75,996.46,148.75,180.94,182.00,63.92 ... (2 Replies)
Discussion started by: refrain
2 Replies

3. Shell Programming and Scripting

Save output of updated csv file as csv file itself

Hi, all I want to sort a csv file based on timestamp from oldest to newest and save the output as csv file itself. Here is an example of my csv file. test.csv SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0739.JPG,2015:02:17 11:32:21 /home/intannf/foto/IMG_0749.JPG,2015:02:17 11:37:28... (10 Replies)
Discussion started by: refrain
10 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

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

6. Shell Programming and Scripting

File Comparison: Print Lines not present in another file

Hi, I have fileA.txt like this. B01B02 D0011718 B01B03 D0012540 B01B04 D0006145 B01B05 D0004815 B01B06 D0012069 B01B07 D0004064 B01B08 D0011988 B01B09 D0012071 B01B10 D0005596 B01B11 D0011351 B01B12 D0004814 B01C01 D0011804 I want to compare this against another file (fileB.txt)... (3 Replies)
Discussion started by: genehunter
3 Replies

7. UNIX for Dummies Questions & Answers

Determines a file is a NORMAL text file

I assign the variable name=`find . -type f | awk -vR=$num '{if (NR == R) {print; exit}}` which returns the name of a file in "$num" ordered in the folder to the variable $name. I want to use IF and determine if the file is a NORMAL text file (does not contain an extension), the command will... (1 Reply)
Discussion started by: suyaku92
1 Replies

8. UNIX for Dummies Questions & Answers

[URGENT] Determines a file is a NORMAL text file

I assign the variable name=`find . -type f | awk -vR=$num '{if (NR == R) {print; exit}}` which returns the name of a file in "$num" ordered in the folder to the variable $name. I want to use IF and determine if the file is a NORMAL text file (does not contain an extension), the command will... (1 Reply)
Discussion started by: suyaku92
1 Replies

9. Shell Programming and Scripting

CSV file comparison

Hi all, i have two .csv files. i need to compare those two files and if there is any difference that should be moved into third .csv file. example, org.csv and dup.csv when we compare those two files org.csv and dup.csv. if there is any change in dup.csv. it should be capture in third... (7 Replies)
Discussion started by: baskivs
7 Replies
Login or Register to Ask a Question