Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Suppose you have one CSV file with 2 fields: ID and email. You have another file with 2 fields: email and name. How can you produce a file with all three fields joined on email?

share|improve this question
5  
A little more detail on the join (i.e., inner, outer, left). Is the email list on the 1st CSV identical to the second list? Or does one contain more? –  hyperslug Aug 20 '09 at 23:29
    
Examples of the csv files would be handy to, along with the OS you are using? –  Troggy Aug 20 '09 at 23:35
    
i think 1st and 2nd list are identical. I am using Linux. Please help!!! thanks!! :) –  crst53 Aug 21 '09 at 0:00
1  
how large is the data? –  Joshua Aug 21 '09 at 0:13

4 Answers 4

up vote 15 down vote accepted

Revision3:

You must sort both lists on email alphabetically, then join. Given that the email field the 2nd field of file1 and the 1st field of file2:

sort -t , -k 2,2 file1.csv > sort1.csv
sort -t , -k 1,1 file2.csv > sort2.csv
join -t , -1 2 -2 1 sort1.csv sort2.csv > sort3.csv

parameter meaning

-t ,   : ',' is the field separator
-k 2,2 : character sort on 2nd field
-k 1,1 : character sort on 1st field
-1 2   : file 1, 2nd field
-2 1   : file 2, 1st field
>      : output to file

produces

email,ID,name
email,ID,name
...

sorted by email alphabetically.

Note that if any email is missing from either file it will be omitted from the results.

share|improve this answer

The easiest resolution of the problem - make xlsx from csv and use Vlookup to search for the name in second file :)

share|improve this answer
6  
File extension xlsx implies Microsoft Excel and I think VLOOKUP does as well. This question is tagged with Linux. Is Microsoft Excel available for Linux? –  Peter Mortensen Mar 11 '11 at 22:26
    
Now LibreOffice has VLOOKUP too. –  Cristian Ciupitu Jun 3 at 19:20

Perhaps it is overkill, but you could import into a database (e.g. OpenOffice Base) as two kinds of tables and define a report that is the desired output.

If the CSV import is a problem, then a spreadsheet program (e.g. OpenOffice Calc) can do the import. The result can then easily be transferred to the database.

share|improve this answer

As a future reference you might want to start playing around with AWK. It's a very simple little scripting language that exists in some form on every *nix system and its sole mission is life is the manipulation of standard delimited textual databases. With a few lines of throwaway script you can do some very useful things. The language is small and elegant and has a better utility/complexity ratio than anything else I am aware of.

share|improve this answer
    
Perl is in many ways a successor of awk. –  reinierpost Sep 14 '10 at 11:33
    
awk doesn't handle quoting and escaping (e.g. dealing with ,s in a ,-separated CSV file) as far as I know. If you need that, using a dedicated CSV handling library is easier; they exist for many languages. –  reinierpost Sep 14 '10 at 11:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.