One time data conversion in Oracle tables with excel data

Question

First of all I have very basic knowledge in back end

I need to do an one time data conversion in an Oracle table with a excel sheet data.

Excel sheet have data in below format

OldData NewData
12130 12130
12810 12810
17330 17331

So what I need to do is , I have to compare old data with one of the column in an Oracle table(say a colum called Modifydata in masterdaataTable) and if any column value matches with OldData value in excel , then replace that value with Newdata value from Excel sheet.

I have

OldData NewData
12130 12130
12810 12810
17330 17331

Note:

1. I have tried using external tables. Since I don't have the privileges to create directory and all, it didn't work out.

2. I have tried "'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\foldername\spreadsheetname.xls;'," logic also.But seems this will work only for MS SQL DB.

• teylyn: cross posted at stackoverflow.com/questions/31376942/… -- please don't post the same question on multiple sites.
• Thomas: I'm sorry about that.Until today I was not aware of dba.stackexchange.com.So I thought many people visit only either of this sites.That is why I cross posted.I will keep this in my mind and delete the post from one of the sites....One more question - , Am I suppose to add db related questions only in db.stackexchange?
• Jack Douglas: There is some overlap between the sites - just use your judgement to chose one or the other depending on the expertise you are seeking. Have a read of the help pages to find out what is on-topic here too.
• Thomas: @Jack - Thanks a lot for your time. This information helps.

Answer 1

If you do not have access to create Directory, then you can use SQL Loader to get data into the database (provided you have Oracle client installed on your system). An outline of the steps is provided below:

a.) Create a table with the same structure as your excel file

b.) Convert your Excel File to .csv format file

c.) Create a Loader Control File. Refer the link SQL Loaer Reference Guide

d.) Go to command line and execute sqlldr command with appropriate format

e.) Verify the bad file and err logs to find if there are any rejections and run them again.

f.) run the update between new table and your existing table.

In case you do not have Oracle Client available, then you can use the data import facility provided in various IDEs like Toad and SQL Developer.

• Thomas: I guess this should work..Didn't know abt sql loader before..While researching on sql loader , happened to see this helpful link steve-lyon.blogspot.in/2013/07/…