mahjag
asked on
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..
ASKER
can you create an example and show me the steps - do I create external table each time the user changes data?
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
>>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
ASKER
is there any VB script from spreadsheet that could update oracle - I am looking for all other solutions as well..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..?
>>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
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
ASKER
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..
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..
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).
One thing you could do is delete the data in the table prior to the upload (be careful when doing so).
ASKER
did not get TOAD solution
In Oracle, create an external table linked to the CSV file
Update your initial Oracle table with the values from the external table.