Combining data from Excel with database

Question

This is probably a simple question, but I really don't know what I'm doing in Excel, so hopefully someone can help me out.

I've been given an Excel spreadsheet that has two relevant columns to my task. The first column is an "External ID", and the second column is an "Internal ID". I need to select a bunch of data out of our databases (with various joins) using the Internal ID as the key, but then all of this data needs to be linked back to the External ID, and the only link between Internal/External is this spreadsheet.

For example, say a row of the spreadsheet looks like this:

ExtID    IntID
AB1234   2

I need to select all the data relevant to the item with ID #2 in our database, but I have no way to get "AB1234" from the database, so I need to somehow relate this data back to "AB1234" using the spreadsheet.

What's the easiest way to accomplish this? The version of Excel is Excel 2007, and the database is Oracle, if that's relevant.

Note that I only have read permission to the production databases, so creating tables and importing the spreadsheet data to do a join is not an option.

Answer 1

Usually I use copy-paste and a good column-mode editor with macros to accomplish such tasks. It works fine if you only have a couple of Excel files.

Answer 2

Alot depends on how familiar you are with the tools you have available to you.

DO you have a tool you are familiar with that would make it easy to use the IntID to find those records? If so, can you do the query and paste the results back into the original spreadsheet in the column to the right of the column with the IntID?

If so, you will have what you want, a spreadsheet with the following columns: ExtID (original) IntID (original) IntID (from Oracle) Col1 (from Oracle) Col2 (from Oracle) etc....

Answer 3

Edited based on a comment

1 - Use MS Access to import the Excel sheet as a table.

2 - Link to your database table, also from within MS Access

External Data tab->other data sources->ODBC connection->choose yours->pick the table(s) you want

3 - Write an Access query to compare the values you want

Create->Query Design->Drop the tables you want, drag lines between them for relationships, click Run

Answer 4

I'm not familiar with Oracle, but I know a lot of databases let you prepend a table name with # or something like that and create a temp table. Others have a temporary database where you can create things. Sometimes you can create a temp table even if you can't do anything else but select.

If you have access to do that, I would do the function as JosephStyons suggests (#2), insert your records into the temp table, and do a query based on that.

Answer 5

With Excel and VBA, you can use ActiveX Data Objects (ADO) as a high level way of using the OLE DB provider for a particular database. This lets you read the data from the database and you can then query that data and store the results in the spreadsheet.

Oracle OLE DB provider
ADO Guide