0

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.

Improve this question
4
  • cross posted at stackoverflow.com/questions/31376942/… -- please don't post the same question on multiple sites.
    – teylyn
    Commented Jul 13, 2015 at 8:41
  • 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?
    – Thomas
    Commented Jul 13, 2015 at 9:05
  • 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.
    – Jack Douglas
    Commented Jul 13, 2015 at 11:04
  • @Jack - Thanks a lot for your time. This information helps.
    – Thomas
    Commented Jul 13, 2015 at 12:59

1 Answer 1

Reset to default
3

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.

Improve this answer
1
  • 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/…
    – Thomas
    Commented Jul 13, 2015 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.