How to copy the data from Excel to oracle?

Question

How to copy the data from Excel to oracle?

Answer 1

Perhaps some combination of DBD::Oracle, DBD::Excel and DBIx::Copy? But surely there's an easier way...

Answer 2

The simplest way I can think of is to put Access in the middle. Attach to Excel (or import the data into Access); then attach to the destination Oracle tables and copy. The Access Export facility also works pretty well.

Answer 3

If its a once off, or rare thing, and you can export to csv, then the Application Express or SQL Loader facilities would work fine. If its a regular thing, then Chris's suggestion is what I'd go with.

Answer 4

There are many different methods, depending 
upon the amount of data, the repetitiveness 
of the process, and the amount of programming 
I am willing to invest.

First, create the Oracle table, using the 
SQL CREATE TABLE statement to define the table's 
column lengths and types. Here's an example of a 
sqlplus 'CREATE TABLE' statement: 

CREATE TABLE SPECIES_RATINGS 
(SPECIES VARCHAR2(10),
COUNT NUMBER,
RATING VARCHARC2(1));

Then load the data using any of the following 
methods or an entirely new method you invent:

--------------------------------------------

First load method:

I use the SQL*Loader method.
You will need to save a copy of your spreadsheet 
in a text format like CSV or PRN. 

SQL*Loader Control file for CSV file:

load data
 infile 'c:\data\mydata.csv'
 into table emp
 fields terminated by "," optionally enclosed by '"'       
 ( empno, empname, sal, deptno )

There are some GUIs that have wizards to walk you through the
process (Enterprise Manager -> Maintenance -> Data Movement ->
Move Row Data -> Load Data from User Files) for the 
ad-hoc imports. Toad for Oracle has a SQL*Loader Wizard as
well. (DBA -> Data Import/Export -> SQL*Loader Wizard)

You can save your Excel data in PRN format if you are 
planning to use positional data (fixed length) in your 
control file.

SQL*Loader Control file for PRN file:

load data
 infile 'c:\data\mydata.prn'
 replace
 into table departments
 (  dept     position (02:05) char(4),
    deptname position (08:27) char(20)  )

Position(02:05) will give the 2nd to the 5th character 

Once I've gone through the EM or Toad wizard, I save 
the control file, tweak it as needed in a text editor, 
and reuse it in SQL*Plus scripts.

SQL*Loader is handy also since it allows you to 
skip certain data and call filter functions (i.e.
native functions as in DECODE() or TO_DATE() or 
user defined functions) in your control .ctl file.

You can load from multiple input files provided 
they use the same record format by repeating the 
INFILE clause. Here is an example:

LOAD DATA
  INFILE file1.prn
  INFILE file2.prn
  INFILE file3.prn
  APPEND
  INTO TABLE emp
  ( empno  POSITION(1:4)   INTEGER EXTERNAL,
    ename  POSITION(6:15)  CHAR,
    deptno POSITION(17:18) CHAR,
    mgr    POSITION(20:23) INTEGER EXTERNAL
  )

You can also specify multiple "INTO TABLE" clauses 
in the SQL*Loader control file to load into multiple 
tables.

LOAD DATA
 INFILE 'mydata.dat'
 REPLACE
 INTO TABLE emp
      WHEN empno != ' '
 ( empno  POSITION(1:4)   INTEGER EXTERNAL,
   ename  POSITION(6:15)  CHAR,
   deptno POSITION(17:18) CHAR,
   mgr    POSITION(20:23) INTEGER EXTERNAL
 )
 INTO TABLE proj
      WHEN projno != ' '
 (  projno POSITION(25:27) INTEGER EXTERNAL,
    empno  POSITION(1:4)   INTEGER EXTERNAL
 )

With SQL*Loader, you can selectively load only
the records you need (see WHEN clause), skip 
certain columns while loading data (see FILLER 
columns) and load multi-line records (see
CONCATENATE and CONTINUEIF)

Once you've created the control file, you need 
to start sql loader from the command line like this:
sqlldr username/password@connect_string control=ctl_file.ctl log=log.log 

You can create a batch file to call sqlldr.

For more examples, see
http://examples.oreilly.com/orsqlloader/

That's it for the versatile SQL*Loader.

--------------------------------------------

Second load method: 

In this scenario, I have full control of the 
spreadsheet, but less control of the data because 
users send me the spreadsheets back with data. 

I create another worksheet within the same Excel 
file, which has locked down INSERT statements 
referring back to the sheet with the data. When 
I receive the spreadsheet, I copy and paste the 
INSERT statements directly into SQL*Plus, or 
indirectly staging them in a SQL script.

Excel is a great tool for composing dynamic 
SQL statements dynamically. (see Excel functions)

--------------------------------------------

Third load method:

If you need a utility to load Excel data into 
Oracle, download quickload from sourceforge at 
http://sourceforge.net/projects/quickload 

--------------------------------------------

Fourth load method:

In theory, this should work.

Configure Generic Database connectivity (Heterogeneous Database HS)
Connect to the Excel spreadsheet from Oracle through ODBC.
Describe it (see DESC command) or 
CREATE TABLE AS SELECT col1, col2 FROM ExcelTable 
to make a copy and see what data types Oracle assigns
the columns by default.

http://www.e-ammar.com/Oracle_TIPS/HS/configuring_generic_database_con.htm

--------------------------------------------

References:

http://209.85.173.132/search?q=cache:GJN388WiXTwJ:www.orafaq.com/wiki/SQL*Loader_FAQ+Oracle+control+file+columns&cd=3&hl=en&ct=clnk&gl=us

http://forums.oracle.com/forums/thread.jspa?threadID=305918&tstart=0

http://techrepublic.com.com/5208-6230-0.html?forumID=101&threadID=223797&messageID=2245485

http://examples.oreilly.com/orsqlloader/

Answer 5

A DBA once showed me an easy trick:

In someplace like another sheet, create a formula like:

INSERT INTO my_table (name, age, monkey) VALUES ('" & A1 & "', " & B1 & ", '" & C1 & "');"

Copy/paste it into the appropriate rows (Excel automatically changes your formula to A2, A3, etc.)

Then copy/paste the result into sqlplus.

Answer 6

Use external tables