Which is the best way to do this ETL job?

Question

I have some data in excel with coulmns and i need to migrate this data from excel to some tables in Oracle. How do I achieve this in best and quick way.? I need to load 5 tables in Oracle from that single excel file. Any tools available for this kind of task?

Answer 1

An approach could be the following.

Say you have two tables like these

create table tabA (col_a number, col_b number);
create table tabB (col_b number, col_c varchar2(10));

and you need to load data starting from a a file d:\file.csv like this:

col_a,col_b,col_c
10,99,"a"
20,999,"b c d"

you first need to create a directory, then build a table containing the CSV:

CREATE DIRECTORY dir_d AS 'D:\';
CREATE TABLE csvFile
(
    Col_a                                      VARCHAR2(4000),
    Col_b                                      VARCHAR2(4000),
    Col_c                                      VARCHAR2(4000)
)
ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
          DEFAULT DIRECTORY dir_d
              ACCESS PARAMETERS
       (RECORDS DELIMITED BY NEWLINE
        SKIP 1
        FIELDS
            TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '"'
            LRTRIM
            MISSING FIELD VALUES ARE NULL
      )
          LOCATION('file.csv'));

Now you can use SQL to insert data in your tables starting from the CSV table, using every logic you need; for example:

insert into tabA select col_a, col_b from csvFile;
insert into tabB select col_b, col_c from csvFile;

SQL> select * from tabA;

     COL_A      COL_B
---------- ----------
        10         99
        20        999

SQL> select * from tabB;

     COL_B COL_C
---------- ----------
        99 a
       999 b c d

Answer 2

if you just want to put the data into one table you can use sqldeveloper to import data into table, like show of this tutorial:

Answer 3

Oracle's SQL*Loader tool can handle multi-table inserts. If you don't want to go to the trouble of building an appropriate control file, you can first load the data into a staging table using a your tool of choice.

From the staging table you can then perform individual inserts, or use a conditional(?) muti table insert statement.:

insert all into table_a (cola, colb, colc) values (col_a, col_b, col_c)
           into table_b (cola, colb, colc, cold) values (col_a, col_b, col_c col_d)
  select col_a, col_b, col_c, col_d from staging_table;

or

insert all 
  when col_a < 1000 then
    into table_a (cola, colb, colc) values (col_a, col_b, col_c)
  when col_a between 900 and 10000 then
    into table_b (cola, colb, colc, cold) values (col_a, col_b, col_c col_d)
  else
    into table_c (cola, colb, colc, cold) values (col_a, col_b, col_c col_d)
  select col_a, col_b, col_c, col_d from staging_table;

With the conditional insert each WHEN clause that evaluate to true would result in data being inserted to the specified table with the ELSE clause only coming into play when no other option applies.