Home » SQL & PL/SQL » SQL & PL/SQL » Data load from Excel to Oracle 10g (merged)
Data load from Excel to Oracle 10g (merged) [message #383844] Fri, 30 January 2009 12:37 Go to next message
sazzadur
Messages: 9
Registered: January 2009
Location: UK
Junior Member
I have several Excel files and I want to load data to Oracle. I saw some articles reagrding this problem but none was helpful. Someone wrote about External table but did not explain how data would be retrieved from Excel and append into Oracle table.
Re: Data load from Excel to Oracle 10g [message #383846 is a reply to message #383844] Fri, 30 January 2009 12:40 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I believe the section on External Tables in the Oracle documentation would give you exactly what you need.

You would need to save as a CSV file first.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm
Re: Data load from Excel to Oracle 10g [message #383863 is a reply to message #383846] Fri, 30 January 2009 15:52 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
A few other options depending on how much time you have, how much data you want to load etc:

Apex has a nice CSV data upload feature.
http://www.oracle.com/technology/products/database/xe/index.html

SQL developer does too.
http://download.oracle.com/docs/cd/E12151_01/doc.150/e12152/dialogs.htm#sthref502

One of the most common ways to get flat files into Oracle if you don't have too many rows (say < 10000), then just reformat your data into insert statements using a text editor & then run the script through sqlplus.

SED can do a good job too:
$> cat my.csv
abc,123,xyz
xxx,111,yyy


$ >  cat my.csv | sed "s/^/insert into MY_TAB values ('/g;s/,/','/g;s/$/')\;/g"
insert into MY_TAB values ('abc','123','xyz');
insert into MY_TAB values ('xxx','111','yyy');





Previous Topic: Select a row with a max value
Next Topic: Export Pictures From Database
Goto Forum:
  


Current Time: Thu Dec 08 12:49:13 CST 2016

Total time taken to generate the page: 0.05289 seconds