Home » RDBMS Server » Server Utilities » Update Oracle table data from data in Excel file
Update Oracle table data from data in Excel file [message #284148] Thu, 29 November 2007 00:54 Go to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
Hello Experts,

I want to update the oracle table data from excel file(.xls). The excel file & table format is same. Actually the excel file is taken from the previous backup of database of the same table.

Now when i try to update the table data by using "import table data" option in toad, only a few records gets updated. It gives some errors like unique constraints violated.

Is there any alternative method to update the table data. The excel file is having near about 18000 records.

Yogesh

[MERGED by LF]

[Updated on: Thu, 29 November 2007 05:23] by Moderator

Report message to a moderator

Re: Importing/Updating data from excel sheet [message #284156 is a reply to message #284148] Thu, 29 November 2007 01:04 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Search about Sql*Loader and External Table.
Re: Update table data from excel sheet [message #284164 is a reply to message #284148] Thu, 29 November 2007 01:25 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

search for EXTERNAL TABLE.

regards,
Re: Update Oracle table data from data in excel file [message #284196 is a reply to message #284148] Thu, 29 November 2007 02:50 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:

It gives some errors like unique constraints violated.

So you are saying that you want to ignore the fact that you are breaking the data integrity rules set up for that table and insert rows that violate the constraints?
Re: Update Oracle table data from data in excel file [message #284198 is a reply to message #284148] Thu, 29 November 2007 02:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no tool which will fix "unique constraint violated" error.

Think twice! Do you REALLY want to allow duplicates in columns which make unique key? If so, just drop the constraint. If not, think again.

BTW, if TOAD does the job, fine. Otherwise, save Excel file as a CSV file and either load it using SQL*Loader or deal with it as an external table (probably a better approach as you need to UPDATE existing records, not INSERT them).
Re: Update Oracle table data from data in excel file [message #284206 is a reply to message #284196] Thu, 29 November 2007 03:02 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
No I do not want to violate the integrity rules. Please explain me the procedure how i can update using SQL Loader or external table concept.

Shall i convert the existing xls file to csv format?
Re: Update Oracle table data from data in excel file [message #284214 is a reply to message #284206] Thu, 29 November 2007 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Shall i convert the existing xls file to csv format?

Yes.

Regards
Michel
Re: Update Oracle table data from data in excel file [message #284231 is a reply to message #284214] Thu, 29 November 2007 03:36 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
After converting to csv format what will be the sql query if suppose my table name is OM_QC_PARAMTETERS and the field names are QCP_QC_CODE,QCP_PARA_CODE,QCP_USL,QCP_LSL.

I want to change the last two fields/ columns data i.e QCP_USL & QCP_LSL where QCP_PARA_CODE='pH10%' AND QCP_QC_CODE='AAAAAA'

For each QCP_PARA_CODE and QCP_QC_CODE i want to change the QCP_USL & QCP_LSL

Please guide me on this.
Re: Update Oracle table data from data in excel file [message #284239 is a reply to message #284231] Thu, 29 November 2007 03:50 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create an external table and select it as you want.

Regards
Michel
Previous Topic: How to load data from one table in a database to another table in another database
Next Topic: Loading data from .dat file
Goto Forum:
  


Current Time: Fri Dec 09 17:14:40 CST 2016

Total time taken to generate the page: 0.58792 seconds