OraFAQ Forum: Server Utilities » Update Oracle table data from data in Excel file

Home » RDBMS Server » Server Utilities » Update Oracle table data from data in Excel file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update Oracle table data from data in Excel file [message #284148]

Thu, 29 November 2007 00:54

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

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Search about Sql*Loader and External Table.

Report message to a moderator

Re: Update table data from excel sheet [message #284164 is a reply to message #284148]

Thu, 29 November 2007 01:25

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

search for EXTERNAL TABLE.

regards,

Report message to a moderator

Re: Update Oracle table data from data in excel file [message #284196 is a reply to message #284148]

Thu, 29 November 2007 02:50

pablolee
Messages: 2882
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?

Report message to a moderator

Re: Update Oracle table data from data in excel file [message #284198 is a reply to message #284148]

Thu, 29 November 2007 02:52

Littlefoot
Messages: 21811
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).

Report message to a moderator

Re: Update Oracle table data from data in excel file [message #284206 is a reply to message #284196]

Thu, 29 November 2007 03:02

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?

Report message to a moderator

Re: Update Oracle table data from data in excel file [message #284214 is a reply to message #284206]

Thu, 29 November 2007 03:18

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Update Oracle table data from data in excel file [message #284231 is a reply to message #284214]

Thu, 29 November 2007 03:36

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.

Report message to a moderator

Re: Update Oracle table data from data in excel file [message #284239 is a reply to message #284231]

Thu, 29 November 2007 03:50

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Create an external table and select it as you want.

Regards
Michel

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 16:17:58 CDT 2024