[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

updating oracle table with excel spreadsheet changed rows

Posted on 2009-12-16
10
Medium Priority
?
561 Views
Last Modified: 2013-12-18
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
Comment
Question by:mahjag
  • 5
  • 5
10 Comments
 
LVL 20

Expert Comment

by:gatorvip
ID: 26066674
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
 

Author Comment

by:mahjag
ID: 26066737
can you create an example and show me  the steps - do I create external table each time the user changes data?
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 26066972
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mahjag
ID: 26073723
is there any VB script from spreadsheet that could update oracle - I am looking for all other solutions as well..
0
 
LVL 20

Accepted Solution

by:
gatorvip earned 375 total points
ID: 26080825
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
 

Author Comment

by:mahjag
ID: 26132170
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
 
LVL 20

Expert Comment

by:gatorvip
ID: 26137829
>>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
 

Author Comment

by:mahjag
ID: 26152733
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
 
LVL 20

Expert Comment

by:gatorvip
ID: 26170543
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
 

Author Closing Comment

by:mahjag
ID: 31667023
did not get TOAD solution
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question