Excel to Oracle Database

Su.gi

Hi All,

  I am using Oracle 11g database and Microsoft Excel 2007.

In Excel, by using Connection wizard, i have connected to Oracle Database and created table 'TEST' in my excel sheet.

Once if I do any DML operation in Oracle database then i reflected in my Excel.

My requirement is,

  If I change in Excel, then it should be affected in Oracle Database...

How to do this?

Thanks,

Su.gi

Pavan Kumar

Hi,

You have so many sources, just search or give a try it's basic ODBC Connection

http://docs.oracle.com/cd/E11882_01/owb.112/e10582/loading_ms_data.htm#WBDOD10660

Demo link :  https://www.youtube.com/watch?v=Adz0zZFePf8

- Pavan Kumar N

Su.gi

Hi Pavan,

  Thanks for your reply.

I have done the above process....oracle table to excel.

if i changes any row value in that excel, it can affect in oracle table???

Thanks,

Su.gi

Pavan Kumar

You can try and see, else you can give a try

http://www.techzavia.com/using-microsoft-excel-spreadsheets-to-update-data-in-an-oracle-database/#.VYRIkkYySO4

jgarry

The hoopercharles blog has much detail on how to make Excel an actual data entry program, worth checking out if that the goal.

I receive a lot of spreadsheets to load, but to have Excel do all the checking that the proper data entry program does for that data is just way too much.  I do it the old fashioned way - ftp the csv over, massage it with appropriate shell programs to find the usual dirtiness, then test load, then real load.  That still doesn't address the problem of users entering bad data, but it does reduce it to that, mostly.  The app has an option for creating the spreadsheets, so at least they can start with good data, I just have to tell them to use csv format.  It takes a few rejection cycles for the keep leading zeros type issues to sink in for some users.

There are other solutions for other situations.

Su.gi

Thanks Pavan,

  your link is helpful for me.

Can you please give any solution for below scenario

Excel is created based on Oracle table with ODBC connectivity.

The table is created in excel as same as in Oracle Database like below link

https://www.youtube.com/watch?v=7Cr-1Dn38ok

If i did any DML operation in database, it affects in excel table.

If i did any changes in excel, then it should be affected in database. This is my requirement.

Thanks,

Su.gi

Su.gi

Thanks for your reply.

Can you please give any solution for my above reply..

Its just single table operation..

Is it possible to do DML operation in excel, it should be affected in my database table.

Thanks,

Su.gi

Pavan Kumar

You may need to write custom macros for that.

Charles Hooper

Su.gi,

See the following link, and then scroll down to the Excel section:

https://hoopercharles.wordpress.com/archived-blog-articles/

I suggest taking a look at two of the articles:

Select From or Update a Database Table Based on the Contents of an Excel Spreadsheet

Transfer a Text File into an Oracle Database using an Excel Macro

The "Oracle Database Time Model Viewer in Excel" article series will show you how to open the database connection, and keep it open until the workbook is closed.

If you have Microsoft Access, it is designed for doing what you require.  In Microsoft Access, just create a linked table using an ODBC connection to the Oracle database.  When you make a change in the data in Access, Access updates the source table in the Oracle database.  Microsoft Access is also able to query an Excel worksheet, just as if it were a database table, so you could then select from the Excel worksheet and insert those rows into an Oracle database table, if necessary.

If you do not have Microsoft Access, it is possible to run a macro when specific cells in the Excel spreadsheet change - the macro would then open the database connection, if not already open (hint, keep that connection open until the workbook is closed), and then update the database table based on the change.  See the following link:

https://support.microsoft.com/en-us/kb/213612

Joel, thank you for the mention of my blog.

Charles Hooper

IT Manager/Oracle DBA

K&M Machine-Fabricating, Inc.

Su.gi

Thanks Pavan,

  How to write custom macros for the insert operation and update operation?

Thanks,

Su.gi

Unknown

Su.gi wrote:

Thanks Pavan,
  How to write custom macros for the insert operation and update operation?

Thanks,
Su.gi

Excel is OFF TOPIC for this forum!