Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 19 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

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

Use csvkit:

csvjoin -c email id_email.csv email_name.csv

or

csvjoin -c 2,1 id_email.csv email_name.csv
share|improve this answer
    
Why isn't this the top answer? – alexg Oct 28 '15 at 9:14

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

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 '14 at 19:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .