Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4290
  • Last Modified:

update Oracle table with data from Excel spreadsheet

I have an Oracle table that I need to update one column in the table for several thousand rows.  I have the data to do that update in an excel spreadsheet.  How can I update that Oracle table with the data in the excel spreadsheet.  I have the unique identifier in the excel spreadsheet for each table row that needs to be updated.  Thanks for any help that you can give.
0
newtoperlpgm
Asked:
newtoperlpgm
  • 4
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
You 'could' write a lot of VBA code but here is what I would do.

Save the Excel spreadsheet as a CSV.
Either use SQL Loader to load the data into a temp table in Oracle or create an External table against the CSV.

Then perform the update from the temp or external table.

There are tons of sql loader and external table examples out there on the web.

Hopefully the update is straight forward.

If you need any help with any of this, please post back with sample data and expected results and what pieces you need help with.
0
 
Steve WalesSenior Database AdministratorCommented:
I have always done something similar in same kind of circumstance.  I have a Delphi piece of template code that opens a spreadsheet and loops through the data and I generate a text file with the insert or update statement then run that through sqlplus.
0
 
slightwv (䄆 Netminder) Commented:
>>and I generate a text file with the insert or update statement then run that through sqlplus.

Didn't think about brute force.

Alternatives without the delphi piece:

global search and replace from a txt/csv file in notepad.
or
I have seen people insert new columns in Excel with the DML then save to txt and replace the tabs.

For example:
insert columnA and add: update some_table set some_column='
columnB has the value
insert columnC: ' where comeothercolumn='
columnD has the value
Insert columnE ';

then save, repace the tabs for the column breaks and run in sqlplus.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Steve WalesSenior Database AdministratorCommented:
Sometimes the brute force approach is the simplest way to get the job done.

A little effort the first time, then super easy each successive time you need to complete a similar task.
0
 
slightwv (䄆 Netminder) Commented:
Could not agree more
0
 
newtoperlpgmAuthor Commented:
I was able to easily create an Oracle staging table, feed the data into the data from the spreadsheet and update my original table with a simple update statement.  Thanks so much for making it easy for me.
0
 
slightwv (䄆 Netminder) Commented:
No problem.  Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now