Visit Our UNIX and Linux User Community


Comparing 2 csv files and matching content


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Comparing 2 csv files and matching content
# 1  
Old 08-06-2009
Comparing 2 csv files and matching content

Hello,

I have the following problem:

There are two csv files

csv-file #1:
Code:
aaa1, aaa2, ... aaan
aaa1, bbb2, ... bbbn
aaa1, ccc2, ... cccn
bbb1, bbb2, ... bbbn
...
zzz1, zzz2, ... zzzn

csv-file #2:
Code:
aaa1, matchvalue1
ccc1, matchvalue2
bbb1, matchvalue3
...

Each line of csv-file #1 shall get appended the crresponding "matchvalue" of csv-file #2,
e.g. if a line contains the reference value aaa1, this line shall be appended by matchvalue1 of csv-file #2.

Finally csv-file #2 would look like this:

Code:
aaa1, aaa2, ... aaan, matchvalue1
aaa1, bbb2, ... bbbn, matchvalue1
aaa1, ccc2, ... cccn, matchvalue1
bbb1, bbb2, ... bbbn, matchvalue3
...

In csv-file #1 the "reference values" - in my example the first element of the line, may occur in more than one line.

In the example it is the first element of each line.
How would the script look like if it may be at any other particular position of the line, e.g. always the third element of each line ?

Can anyone please help me and submit a solution ?

Thanks so much !

Regards
G.
# 2  
Old 08-06-2009
U can start with:
Code:
awk 'NR==FNR{a[$1]=$2;next}{$0=$0","a[$1]}1' FS=',' csv-file2 csv-file1

# 3  
Old 08-26-2009
Thanks for the reply, Klashxx.
But I get back an error message:

awk: Syntax error next to line 1
awk: Bailing out near line 1

I entered the sequence (with fitting filenames) at the prompt, so did not write it to a file and start that, right ?

Both files are in the same directory. Same error also when I add the path.

What could be the reason ?
OS is Solaris 9 and awk is - of course - on the machine.

Are there versions of awk that do not understand all command switches ?

Does anyone knwo an other solution for my problem maybe ?

Thanks in advance.
# 4  
Old 08-26-2009
try '/bin/nawk' or '/usr/xpg4/bin/awk' instead of old/broken '/bin/awk'
# 5  
Old 08-26-2009
With join:
Code:
 sort file2 | join -t, -11 -21 file1 -

# 6  
Old 08-26-2009
Thanks vgersh99 !
Actually I was just asking myself if there's an alternative, when I found that "nawk", which I didn't know before.
Yes, it was on the machine :-)
And it worked perfectly.
Was about to close this thread when I found your kind hint, which crossed with my own investigations.

So, thanks to both of you, Klashxx and vgersh99. You made my day.

I think we should compile kind of "Cookbook For Solving the Most Common Issues Using Shell Tools".

I think the problem I needed a solution for is a very common one: matching various "check lists" and adding one (or more) items to each line of one file if there's a match on the other.

And those "crude" shell tools work so damned fast...

Again thanks so much for helping me

---------- Post updated at 10:12 PM ---------- Previous update was at 10:09 PM ----------

Hi once more,
how would your solution have to be modified in case the "reference column" of csvfile #1 is not the first column but e.g. the third column ?

I tried with

Code:
awk 'NR==FNR{a[$1]=$2;next}{$0=$0","a[$3]}1' FS=',' csv-file2 csv-file1

but it found only 1 hit and other lines just ended on a colon.

---------- Post updated at 10:58 PM ---------- Previous update was at 10:13 PM ----------

Hi once more,
how would your solution have to be modified in case the "reference column" of csvfile #1 is not the first column but e.g. the third column ?

I tried with

Code:
awk 'NR==FNR{a[$1]=$2;next}{$0=$0","a[$3]}1' FS=',' csv-file2 csv-file1

but it found only 1 hit and other lines just ended on a colon.
# 7  
Old 08-27-2009
Can you post samples of the real input files and desired output?

Previous Thread | Next Thread
Test Your Knowledge in Computers #834
Difficulty: Medium
JSON supports comments.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Comparing two files and list the difference with common first line content of both files

I have two file as given below which shows the ACL permissions of each file. I need to compare the source file with target file and list down the difference as specified below in required output. Can someone help me on this ? Source File ************* # file: /local/test_1 # owner: own #... (4 Replies)
Discussion started by: sarathy_a35
4 Replies

2. Shell Programming and Scripting

Comparing two files by two matching fields

Long time listener first time poster. Hope someone can advise. I have two files, 1000+ lines in each, two fields in each file. After performing a sort, what is the best way to find exact matches where field $1 and $2 in file1 are also present in file2 on the same line, then output only those... (6 Replies)
Discussion started by: bstaff
6 Replies

3. Shell Programming and Scripting

Comparing two CSV files

I have two csv files and im trying to compare them. e.g. SAMPLE DATA: file one: ZipCode Name 20878 Washington 10023 Missouri 20304 Maryland file two: ID Name City ZipCode 11654 ... (11 Replies)
Discussion started by: dan139
11 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

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

6. Shell Programming and Scripting

comparing two files for matching fields

I am newbie to unix and would please like some help to solve the task below I have two files, file_a.text and file_b.text that I want to evaluate. file_a.text 1698.74 1711.88 6576.25 899.41 3205.63 4187.98 697.35 1551.83 ... (3 Replies)
Discussion started by: gameli
3 Replies

7. Shell Programming and Scripting

comparing 2 files and creating third file with uncommon content

I want to compare 2 files and create third file with uncommon content. e.g. file1 ajay suhas tom nisha vijay mahish file2 ajay suhas tom nisha expected output file content vijay mahish Is it possible in single command ? Thanks, Ajay (6 Replies)
Discussion started by: ajaypatil_am
6 Replies

8. Shell Programming and Scripting

comparing csv files

Hi! I'm just new to shell scripting n simple tasks looks so tough in initial stage. i need to write a script which will read a property file, property file will be containing count of the csv files, and in a folder(same folder) there will be respective csv files. like Property file data1=100... (3 Replies)
Discussion started by: sukhdip
3 Replies

9. Shell Programming and Scripting

AWK - Comparing/Matching/Counting with 2 files

I have 2 files that I want to do some comparing on. First, I want to find the unique list of devices in file1 and then put them to a new file, file2. I was able to do this without any problem with the following statement: cat file1 | awk '{print $2}' | awk '!x++' > file2Here is what I can't... (2 Replies)
Discussion started by: jontjioe
2 Replies

10. Shell Programming and Scripting

comparing files content

hi i have a set of files , i need to compare one file content with other file content, i am using cmp -s abc.1 def.2 , but it is not giving theproper o/p even if the content is different.Please help thanks Satya (1 Reply)
Discussion started by: Satyak
1 Replies

Featured Tech Videos