Fastest way to map a list of names in an excel doc to their IDs in a lookup table?

Question

For one of my projects I display a list of counties in a drop down list (this list comes from a lookup table containing all counties). The client just requested that I limit it to a subset of their choice. The subset was given to me in an excel spreadsheet containing only names (seen below):

I'm trying to figure out the quickest way possible for me to map each of these to their corresponding id in the original lookup table. The client cannot give me these IDs. The names in here match the names in my table (except for the case).

This will most likely be a one time only thing.

Can anyone suggest a fast way to get these values into a query so I don't have to manually do it?

When I say fast I'm not talking about processing speed, just the fastest start to finish time that results in me getting the corresponding IDs using any tool available.

Note: I'm aware that I could have probably done this manually in the time it will take to get an answer, but I'd like to know for future reference.

Answer 1

1. Put the list in a text file. Write a powershell script which will get the contents of that file and then query your database to output the keys. Here is a rough, rough example.

   get-content c:\list.txt | ForEach-Object {invoke-sqlcmd -E -query"select blah blah where county =" _$} | Format-Table

2. If you have access to SSIS, you could probably do a join between the excel source and your table.

3. You could load the excel sheet in to a temp table to take advantage of all your SQL query knowledge.

4. I believe (and yes it is true) that SQL can create a linked server out of a spreadsheet. Then you get to joining and you're done.

Answer 2

I normally do something like this.

In SSMS type select * from counties where name in ()

In column B1 put the formula ="'" & SUBSTITUTE(A1, "'", "''") & "'," and copy down

paste the results into the original query, remove trailing comma and run

Answer 3

You could do an External Data Query into another Excel sheet with

SELECT countryname, countryid FROM countries

then use a VLOOKUP to get the ids into the client provided sheet

=VLOOKUP(A1,Sheet2!$A$1:$B$200,2,FALSE)

See http://excelusergroup.org/blogs/nickhodge/archive/2008/11/04/excel-2007-getting-external-data.aspx for creating External Data Table in Excel 2007. Skip down to the "Other Sources" part.