Toad World® Forums

Joining Excel and Oracle DB in Query


#1

I am new to TDA, and am trying to join an Excel table with a table inside Oracle. Is this possible? I can’t seem to get it to work. I have version 1.1.1. Thanks for your help.


#2

Yes. This is very easy to do. You need to make a connection to the Excel file. In all versions prior to TDA 2.7 you had to define a name range before connecting. In TDA 2.7 we do that for you. It is a lot easier to connect to Excel in TDA 2.7. Can you upgrade to this version?

After you connect, open up a Query Builder and from the object palette drag in a table. Then double click on your Oracle connection. This will make the current connection Oracle. Then drag in your Oracle table. You will need to manually make your join. To do so, drag one column to the other.

Debbie


#3

Debbie–
Thanks for your repsonse. Unfortunately I dont have the option to upgrade to TDA 2.7 as my company doesnt allow it as of now. I was able to pull tables from Excel and Oracle into a query via the Query Builder, however this is the error I get:

ERROR [HY000] [Microsoft][ODBC Excel Driver] Reserved error (-7778); there is no message for this error.

Any ideas on why it keeps giving me this error?


#4

What version of Excel are you using? And what version of Oracle.

Please post your SQL statement and let me see if I can see what the issue is.

Debbie


#5

SELECT BLU_HSA_INTERFACE_ERRORS.*

INNER JOIN

‘G:\Log.xlsx’.hsa hsa

ON (CVAR(hsa.Coverage) = CVAR(BLU_HSA_INTERFACE_ERRORS.TX_CVG))

AND (CVAR(hsa.Group) = CVAR(BLU_HSA_INTERFACE_ERRORS.TX_GRP_NBR))

AND (CVAR(hsa.Error) = CVAR(BLU_HSA_INTERFACE_ERRORS.MSG_NUMBER))

I am using TDA version 2.0. Any help anyone can give me would be greatly apprecitated.

Hi,

I am also having trouble connecting Oracle 11g to Excel 2007. I can run queries in either alone, but when I try to query both at the same time, I get: [Microsoft][ODBC Excel Driver] ODBC–call failed.

When I change the smartquotes that Toad for Data Analysts put in to regular single quotes I get: [Microsoft][ODBC Excel Driver] ‘‘G:\Problematic_Cases_Log.xlsx’’ is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

This is what the query looks like:
FROM {{Odbc(“DRIVER={Microsoft ODBC for Oracle};CONNECTSTRING=BLU_INQ.WORLD;UID=AHOTHI;PWD={0};,3F16578AFFF7B67F137A2630D0321C76”)}}.“BLU.BLU_HSA_INTERFACE_ERRORS” BLU_HSA_INTERFACE_ERRORSAND (CVAR(hsa.Package) = CVAR(BLU_HSA_INTERFACE_ERRORS.TX_PACKAGE_TYPE))AND (CVAR(hsa.Section) = CVAR(BLU_HSA_INTERFACE_ERRORS.TX_SEC))


#6

By default we use the Microsoft ODBC driver and this driver does not work with Oracle 11G. You need to configure TDA to use the Oracle ODBC driver. To do this, go to your Oracle connection properties and use the Advanced Tab. There is an ODBC drop down. Select your Oracle ODBC driver.

Debbie