oracle 如何将数据从Excel复制到oracle？

How to copy the data from Excel to oracle?

提问by EvilTeach

How to copy the data from Excel to oracle?

如何将数据从Excel复制到oracle？

回答by

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/

回答by Bill

A DBA once showed me an easy trick:

一位 DBA 曾经向我展示了一个简单的技巧：

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.)

将其复制/粘贴到适当的行中（Excel 会自动将您的公式更改为 A2、A3 等）

Then copy/paste the result into sqlplus.

然后将结果复制/粘贴到 sqlplus 中。

回答by EvilTeach

Use external tables

使用外部表

回答by dkretz

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.

我能想到的最简单的方法就是把Access放在中间。附加到 Excel（或将数据导入 Access）；然后附加到目标 Oracle 表并复制。Access 导出工具也运行良好。

回答by Matthew Watson

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.

如果它是一次性的或罕见的，并且您可以导出到 csv，那么 Application Express 或 SQL Loader 工具将可以正常工作。如果这是常规的事情，那么我会采用 Chris 的建议。

回答by Chris Dolan

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

也许DBD::Oracle、DBD::Excel和DBIx::Copy 的某种组合？但肯定有更简单的方法......