join Two excel tables to return data using Microsoft query

GaryG9595

New Member
Joined
Jun 13, 2014
Messages
48
Hello,
I am having issues filtering data. Is there a way I can pull data from SQL using ODBC but also limit that data to only return a join from a table or sheet I create in excel?
Basically if I pull data from ODBC and I use the query to pull all fields I need to create a table, but then I want to create a list in another spreadsheet, and use some kind of join to return another table with just those names in a particular field or column.
I have a table that I return from odbc with 18,000 names and related columns of data.... I want to create another excel sheet or table with 1,300 different names and return only those 1300 names and the related columns of data....
Any way to do that in excel...? I know I can use access or sql to do it, but I am not as familiar with that.
Oh and I'm using office 365
Thanks,
Gary
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

bbotzong

Board Regular
Joined
Dec 17, 2003
Messages
56
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
Are you using the Query tool? If so, build two separate queries (Data tab, Get & Transform Data Group, Get Data).

Query 1: Select From File, From Workbook and navigate and select your Excel file and OK. Select the sheet and click the dropdown next to Load at bottom and choose Load To. In the dialog box, choose Only Create a Connection and Add this data to the Data model.

Query 2: Select Get Data again, then From Other Source, then ODBC. Select your connection. If you want to only pull in certain columns, click Transform. Select a column then <ctrl>+<click> to select columns you want to return with data. Right click on the header row after you've selected all and click Remove Other Columns. Don't worry, this does NOT affect your source data. When ready, click on the Home Tab, click the down arrown next to Close and Load and select Close and Load To... Same as query 1, select Only Create a Connection and Add this data to the Data model.

Finally, you'll want to create a Merge query. Click Get Data, Combine Queries, Merge. In the top, select your Excel query and in the second, select the ODBC data file. Next, you will create the join. In the top table, click on the Primary key field used to match up the data. In the bottom table, click the corresponding field. Then choose your Join kind. You might get a "Privacy Levels" warning. Read and decide if you want to ignore. Click OK. You should see your fields from the Excel file on the left and your ODBC table name in the right column. Notice there is a split arrow next to the field name. Click it to bring in your data from the ODBC query. UIse the checkboxes to bring in appropriate fields. Since you should have chosen those when creating Query 2, it shouldn't need adjusting. Click OK. Then click CLose and Load. Le Voila... your merged data table from and Excel file and your ODBC source.

Hope this helps and I hope I didn't leave anything out....
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,282
Members
418,185
Latest member
snoogz2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top