Join Excel spreadsheet into SQL database

Question

I often get requests that look something like this:

"Hey, can you reconcile this Excel spreadsheet with our database and fill in the additional info for the ones you find? We also need to know which ones are missing." ID Name Client AdditionalInfo ------------------------------------------ 234 John Client1 147 Mike Client2 546 Mary Client3 ... (Several hundred or thousand more rows) 

Is there a way to get an Excel spreadsheet in the same way and join it in an SQL database in SQL Server Management Studio? Usually I copy ID to create a massive IN list, which is obviously far from ideal. Then I still have the task of arranging the data if I do not get a 1: 1 match for each row.

I am using SQL Server 2005 and Excel 2010.

I tried to add an Excel spreadsheet as a linked server, but a request for it warned me that the 'Ad Hoc Distributed Queries' blocked and should be enabled. I’m not sure what negative consequences such opportunities can have, so I didn’t go further along this route.

My next thought was to use Excel formulas to do SELECT [Col_A], [Col_B], [Col_C] UNION ALL for every single row in Excel, and then copy it to SSMS to create a temporary table into which I might join ... but it still seems pretty dirty and ineffective to hack

Answer 1

You can import an Excel spreadsheet into a spreadsheet in your database created specifically for this reason. In SSMS, right-click your database, go to Tasks-> Import Data and select Excel as the data source.

Answer 2

Excel can connect directly to Sql Server. In particular, you want to use MS Query . This will allow you to join the Excel worksheet directly to the database using SQL.