Tutorial :How to copy the data from Excel to oracle? [duplicate]


This question already has an answer here:

How to copy the data from Excel to oracle?


  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:*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/  


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.


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.


Use external tables


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


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.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »