Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help with comparing columns from a csv file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help with comparing columns from a csv file
# 1  
Old 09-11-2008
Help with comparing columns from a csv file
Hi there,

I have an csv file.
I want to compare the 16th and 18th columns. They contain alpha numeric characters. Some are same and some are different. We have to pick the ones which are different. But with certain rules.

Code:
16th col.                       18th col.
----------                     ----------
AF640D                         AF640A    
AF060                           AF060      
AF6238                         AF6428     
AF976                           AF976      
AF1623                         AF1624
AF676M                         AF338D 
AF456                           AF454

From the given table the 16th column shows AF640D and the 18th one shows AF640A
They both are same(Taking in to account only the first 2 characters(AF) and the following numbers. But neglecting the last character (Eg. D and A).


I want to create a new csv file only with those rows that the 16th and 18th columns are different cosidering the above rule and generate new file.

So the new file should contain the rows from the above example like the following. (Here below is only the 16th and 18th column but it should contain every column)

Code:
AF6238                     AF6428
AF676M                     AF338D
AF456                       AF454
AF1623                     AF1624

I guess the easiest would be with awk but I don't have the knowledge to write this.

Can somebody help?

Thanks in advance
# 2  
Old 09-11-2008
Hammer & Screwdriver One attempt, all in script
The part that might need further scrutiny is where you said to ignore the final character. I assumed it to be alphabetic, and given your sample data simply exclude alpha characters from the 2nd comparison logic.


program is here
Code:
> cat comparef
#! /usr/bin/bash

#delete outfile from previous program executions
rm outfile 2>/dev/null

while read zf
   do
   fld16=$(echo "$zf" | cut -d"," -f1)
   fld18=$(echo "$zf" | cut -d"," -f3)

   fld16_2=$(echo "$fld16" | cut -c1-2)
   fld18_2=$(echo "$fld18" | cut -c1-2)

   if [ $fld16_2 = $fld18_2 ]
      then
         fld16_r=$(echo "$fld16" | cut -c3- | tr -d "a-zA-Z")
         fld18_r=$(echo "$fld18" | cut -c3- | tr -d "a-zA-Z")
      if [ $fld16_r = $fld18_r ]
         then
            echo "2equal" "$fld16" "$fld18"
         else
            echo "$zf" >>outfile
      fi
   fi
                
done<infile

following is the input file, program execution, output file
Code:
> cat infile
AF640D,col17,AF640A    
AF060,col17,AF060      
AF6238,col17,AF6428     
AF976,col17,AF976      
AF1623,col17,AF1624
AF676M,col17,AF338D 
AF456,col17,AF454
> comparef
2equal AF640D AF640A
2equal AF060 AF060
2equal AF976 AF976
> cat outfile
AF6238,col17,AF6428
AF1623,col17,AF1624
AF676M,col17,AF338D
AF456,col17,AF454

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Comparing Select Columns from two CSV files in UNIX and create a third file based on comparision

Hi , I want to compare first 3 columns of File A and File B and create a new file File C which will have all rows from File B and will include rows that are present in File A and not in File B based on First 3 column comparison. Thanks in advance for your help. File A A,B,C,45,46... (2 Replies)
Discussion started by: ady_koolz
2 Replies

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

4. Shell Programming and Scripting

Comparing Columns and writing a new file

I have a table with one column File1.txt 1 2 3 4 5 6 7 8 9 10 Another table with two columns; This has got a subset of entries from File 1 but not unique because they have differing values in col 2. File2.txt 1 a 2 d 2 f 6 r 6 e (3 Replies)
Discussion started by: cs_novice
3 Replies

5. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

6. UNIX for Advanced & Expert Users

Help in Deleting columns and Renaming Mutliple columns in a .Csv File

Hi All, i have a .Csv file in the below format startTime, endTime, delta, gName, rName, rNumber, m2239max, m2239min, m2239avg, m100016509avg, m100019240max, metric3min, m100019240avg, propValues 11-Mar-2012 00:00:00, 11-Mar-2012 00:05:00, 300.0, vma3550a, a-1_CPU Index<1>, 200237463, 0.0,... (9 Replies)
Discussion started by: mahi_mayu069
9 Replies

7. Shell Programming and Scripting

Comparing two csv file fields using awk script

Hi All, I want to remove the rows from File1.csv by comparing the columns/fields in the File2.csv. I only need the records whose first column is same and the second column is different for the same record in both files.Here is an example on what I need. File1.csv: RAJAK|ACTIVE|1... (2 Replies)
Discussion started by: rajak.net
2 Replies

8. Shell Programming and Scripting

Comparing columns in a file

I have two files. One a small one and another one is big. The smaller one look like this: Filename: 1.tmp 3453 0 326543 1 2321 0 3212 1 The big file looks like this: Filename 1.res 0.3232 2321 9.2922 123 0.983 3212 8.373 326543 0.9 3453 1.098 3432 I want to extract those lines... (2 Replies)
Discussion started by: shoaibjameel123
2 Replies

9. UNIX Desktop Questions & Answers

COMPARING COLUMNS IN A TEXT FILE

Hi, Good day. I currently have this data called database.txt and I would like to check if there are no similar values (all unique) on an entire row considering the whole column data is unique. the data is as follows cL1 cL2 cL3 cL4 a12 c13 b13 c15 b11 a15 c19 b11 c15 c17 b13 f14 with... (1 Reply)
Discussion started by: whitecross
1 Replies

10. 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
Login or Register to Ask a Question