• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

updating oracle table with excel spreadsheet changed rows

I have a table in oracle which has null values for certain columns and I gave the user to fill the values and they got back the spreadsheet with the values, now I want to populate them back from spreadsheet to table. - I have oracle 10x and TOAD  - is there any easier way as it is going to be repeated process..
0
mahjag
Asked:
mahjag
  • 5
  • 5
1 Solution
 
gatorvipCommented:
Export your spreadsheet to CSV, save it to a directory accessible to Oracle
In Oracle, create an external table linked to the CSV file
Update your initial Oracle table with the values from the external table.
0
 
mahjagAuthor Commented:
can you create an example and show me  the steps - do I create external table each time the user changes data?
0
 
gatorvipCommented:
Sorry, just saw that it's supposed to be a repeated process. Instead of moving spreadsheets back and forth (which seems tedious at first glance, for a simple data update), have you considered hooking up the other user with an ODBC connection and letting them use Excel or Access to update the data directly?


>>do I create external table each time the user changes data?

You wouldn't need to, unless you have structural changes. See this link for some good coverage of external tables:
http://download.oracle.com/docs/cd/B12037_01/server.101/b10825/et_concepts.htm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mahjagAuthor Commented:
is there any VB script from spreadsheet that could update oracle - I am looking for all other solutions as well..
0
 
gatorvipCommented:
Take a look at this article, it's pretty well written and covers all the basics:

http://www.databasejournal.com/features/oracle/article.php/10893_3358411_1/Connecting-with-Oracle-Accessing-Oracle-via-Access-and-Excel.htm

Of course, the user account that you use to connect to the database must have the appropriate rights to the tables you want to query.
0
 
mahjagAuthor Commented:
I started using TOAD import table data but I could not use to update certain rows from spreadsheet to oracle - eventhough the loader has the capability to update - it does not - I am trying to update using non unique key but the loader always trying to reload all data from spreadsheet and errors..can someone suggest solutions..?
0
 
gatorvipCommented:
>>I started using TOAD import table data but I could not use to update certain rows from spreadsheet to oracle

What does this mean? What's the relationship between Toad and the spreadsheet?

>>eventhough the loader has the capability to update- it does not

Which loader? If Toad, this sounds like a Toad issue.

>>I am trying to update using non unique key but the loader always trying to reload all data from spreadsheet and errors

That is probably how it should work
0
 
mahjagAuthor Commented:
Hi gatorvip

I was trying to import data from spreadsheet to oracle databse using TOAD - sorry for not making this clear - updating a non unique key with no changes to unique key should not error - that was my question as to why this happens..
0
 
gatorvipCommented:
Sorry, I don't work with Toad, so I can't really answer that. However, I can guess that all that Toad does is a batch upload into your table - since the data (including PK) is already there, Toad's process will error out as expected.

One thing you could do is delete the data in the table prior to the upload (be careful when doing so).
0
 
mahjagAuthor Commented:
did not get TOAD solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now