Forum Stats

  • 3,837,822 Users
  • 2,262,300 Discussions
  • 7,900,399 Comments

Discussions

Excel to Oracle Database

Su.gi
Su.gi Member Posts: 511
edited Jul 2, 2015 1:26AM in General Database Discussions

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?

doc.png

Thanks,

Su.gi

Tagged:

Answers

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Jun 19, 2015 4:53AM

    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
    Su.gi Member Posts: 511
    edited Jun 19, 2015 7:42AM

    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

  • jgarry
    jgarry Member Posts: 13,844 Gold Crown
    edited Jun 19, 2015 1:28PM

    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
    Su.gi Member Posts: 511
    edited Jun 20, 2015 8:10AM

    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
    Su.gi Member Posts: 511
    edited Jun 20, 2015 8:19AM

    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
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Jun 20, 2015 10:42AM

    You may need to write custom macros for that.

  • Charles Hooper
    Charles Hooper Member Posts: 1,317 Gold Badge
    edited Jun 20, 2015 7:32PM

    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
    Su.gi Member Posts: 511
    edited Jul 2, 2015 1:14AM

    Thanks Pavan,

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

    Thanks,

    Su.gi

  • Unknown
    edited Jul 2, 2015 1:26AM
    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!

This discussion has been closed.