Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

update Oracle table with data from Excel spreadsheet

Posted on 2014-04-16
7
Medium Priority
?
3,707 Views
Last Modified: 2014-04-18
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
Comment
Question by:newtoperlpgm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40005500
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
 
LVL 23

Expert Comment

by:Steve Wales
ID: 40005505
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40005513
>>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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Expert Comment

by:Steve Wales
ID: 40005552
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40005555
Could not agree more
0
 

Author Comment

by:newtoperlpgm
ID: 40007039
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40007074
No problem.  Glad to help!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question