SQL Inner Join possible between Microsoft Excel and ODBC data source?

1
votes

I have a column A on Sheet 1 in Excel with 50 city names (A1:A50), and an ODBC data source with populations for cities around the world in one table (hundreds of thousands of records, not an option to pull it all in to Excel). I would like to do an Inner Join between Sheet 1 and the ODBC data source where city names match (this is the key for the ODBC table). The resulting new table from the Inner Join could open in Sheet 2 upon execution with the 50 city names and their populations.

I have tried using the "Other Sources/ODBC" external data capability in Excel, but so far have been only able to pull in data exclusively from the ODBC source. I have been unable to figure out the Inner Join between an Excel sheet and an ODBC source. Can it be done?

2
sqlexcelodbc

2 Answers

1
votes

The short answer is no.

The longer answer is that you would need to insert the data from the Excel file into the SQL Server database in order to perform the join. There are several tools out there that help you do that. Once the data are in SQL Server, you can join and pull the data any way you want it.

0
votes

The Excel 'power query' tool can link two tables and build allow a data merge. PQ can open large datasets bigger than Excel can normally handle. There are tutorials online that explain this in detail. Brief outline is:

  1. Open the spreadsheet, make a connection to it
  2. Open the ODBC data source, make a connection to it.
  3. Use the merge tool to both items, specify field(s) to link (your City data) and specify the join you want (left join should work for you).

https://www.youtube.com/watch?v=712mV9jwMLo

https://www.ablebits.com/office-addins-blog/2018/11/14/excel-join-tables-power-query/

(PQ is available as an add-in for 2010 & 2013 Excel, but built in 2016, but may need enabling)