Insert MS Excel table into Oracle 10.2 via Oracle Sql Developer or ? 2

[BxWill]

Each week I perform the following;

Copy approximately 6000 account id numbers from one MS Excel worksheet(worksheet 1) to another worksheet (worksheet 2)within the same workbook.

Using advanced filter in MS Excel, obtain the unique account id numbers (approximately 4000 each week).

Save worksheet 2 as a text file.Open MS Access and import text file as table.

Link text file table to Oracle table linking on common field within pre-built MS Access query.

Run MS Access query to extract phone numbers, from Oracle db, for each account id.Copy paste query results to Excel worksheet and use vlookup to "merge" the phone numbers to the other data for each account id.

Total time = 1 hour+!

This needs to be performed in no more than 10 minutes!

Exploring two options;

Option 1 - Import Excel worksheet into Oracle sql developer


Option 2 - Create temporary tables in Oracle (version 10.2) using the "Insert into ##TmpAcctID" syntax into MS Excel range and pasting into the Oracle sql developer pane (similar to what I perform using Mgmt Studio to extract data from Sql Server).

Just wanted to gather some insight before I proceed.

Can both options be performed?

Is it possible to provide the steps for each option above?Which option above is preferred?Thanks in advance

 

[Turkbear]

Hi,
You could create a table in Oracle ( call it a holding_Table_for_New_Excel_data or something simpler!)
Create a Unique Constraint ( or Index) on the AccountID field, then export your Worksheet1 data to that table ( the duplicate AccountIDs will not be accepted)

I am not clear where or what additional data is in Oracle and in Excel [the part about phone numbers to the other data for each account id.] so I am not sure of the next step, but if these accountIDs need telephone numbers ( from some other Oracle table??) then a simple query, linked by AccountId should let you export what is needed.

Once the Holding_table_for_Excel_data has served its purpose, truncate it so it is ready for the next time you need it.



If you can clarify what outcome you need ( data fields and source/destination) more help may be available.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BxWill]

Don't believe that I have rights to create a table in Oracle.

The only data needed from Oracle is the phone number for the customers. By linking on the acount id in my Excel-based table and account id in the Oracle-based table, I am able to extract the phone number.

Along with the two options mentioned in the initial post, I am also contemplating the use of a user-defined function within Excel to extract the desired data.

 

[Turkbear]

Hi,
It may be easier to export the accountID and Phone number from the Oracle table into an Excel worksheet as needed- You ( or the Oracle DBA if needed and willing ) can set that up so that the Export is in a format that Excel can read ( usually CSV text)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BxWill]

Turkbear,

Are you suggesting that if I have 4000 unique id numbers in Excel, then I should just run a query in Oracle to obtain the phone numbers for the unique id numbers and then export as a csv file or export the entire Oracle table and then filter out the account ids that I need???

 

[Turkbear]

Hi,
As I think more about it, while that is one way to do it ( create an Excel worksheet with the entire Oracle table's IDs and Phone Numbers and merge it with your 4000 ID list)
perhaps exploring Oracle's Heterogeneous Connection method ( uses ODBC to connect to a non-Oracle datasource) and use the power of Oracle to create the merged list ( or all your data needs) something like:

[COLOR=red] not really code, just pseudo [/color]
Select distinct PhoneNumber from OracleTable  where 
AccountID in 
(Select AccountID from ExcelDataLink)

Here is a link to more info on those types of connections.

http://oraclebizint.wordpress.com/2...eous-data-sources-excel-mysql-and-sql-server/

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[taupirho]

You might be able to use ODBC (or Oracle Objects for OLE) from within an EXCEL VBA macro to get a connection to the oracle database and read the phone numbers for each ID directy into the excel spreadsheet. This bypasses your messing about with MS access.


In order to understand recursion, you must first understand recursion.

 

[BxWill]

Won't the Oracle Sql Developer allow one to import an Excel file as a table?

Have heard about this feature but it appears to not be available in Oracle Sql Developer version 2.1.1.64.

Anyone familiar with importing Excel worksheets using Oracle Sql Developer?