Excel - take two csv files and map them

Question

I have two csv files. One has a list of emails, another has a long list of emails with first names, last names, addresses, etc. What I am looking to do is take both of these files and map the emails together so that the shorter list of emails has the first name, last name, address, etc from the long list of emails.

Is this possible in Excel? Is this possible at all in Windows or Mac? I hope this makes sense.

Solution

You can use Query from Excel Files :

• Define name for primary table dataset - the short list of emails (Formulas tab -> Define name)
• Define name for secondary table dataset - the long list of emails with additional data
• Go to Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
• Select your csv file and confirm that you want to merge the columns manually
• In the following window "Query from Excel Files", drag&drop the email column of first dataset into the email column of second dataset - a link between these columns will be created
• Go to File menu, click "Return Data to MS Office Excel", an Import Data dialog will pop up
• Select the sheet into which you would like the matched data to be imported
• Click OK -> you should see matched data with columns from both tables

Or if you don't mind uploading your files to an online service, you can use for example http://www.gridoc.com/join-tables and have the rows matched by creating a matching rule (Disclaimer: I am author of the tool).

Hope this helps.

Comments

[user2551750]

the email addresses in a predictable format

[user2551750]

I havent tried anything, I have no idea what to do or what to google.

[natancodes]

If you chop this up to little pieces, you'll need to: Read a CSV file into your Excel sheet. Copy the matching data from one Excel sheet to another. Write a CSV. - That should get you started!

[Sun]

In database terminology, what you called "map" is called a join. You want to join the short list with long list using e-mail address. The e-mail address is what you join on (or the key).