To the best of my knowledge there are 4 different options available to load Excel sheet into Oracle Table. The Below Table illustrates all the 4 ways to load Excel data into oracle tables.
| Ways to load Excel spreadsheet | load data from CSV files by SQL*Loader | load data with external tables | load data by ODBC gateway or utl_file to read the file | load data by Code template or Heterogeneous services |
| Precondition | PL/SQL mapping | A CSV/ text file converted from Excel is required | ODBC gateway for Excel is set up. | |
| Mapping type | No. | SQL*Loader mapping & PL/SQL Mapping if doing more transform | PL/SQL mapping | CT mapping |
| Are staging table and extra mapping required? | High. It can support the full range of Warehouse Builder data transformation capabilities. | Yes when there are more than one source files or mapping is complex. | No. | No. |
| What mapping operators are supported? | Low. Only one flat file source is allowed within each mapping, and only a few operators are applicable. | High. It can offer the full range of Warehouse Builder data transformation capabilities. | Middle. More than one file operators can be involved within one mapping. CT mappings support only a subset of transformations available in PL/SQL mappings. |
For More details reading follow this link: http://docs.oracle.com/cd/E11882_01/owb.112/e10935/sap_km_mappings.htm#WBETL07002
Method1: load data from CSV files by SQL*Loader
Step1: Save the file in .csv format from excel software
Simply open the file in Microsoft Excel and pres “Save As” put the File extension csv and save the file.
Step2: Make one .CTL file
Step3: Now Run the SQLloader command to load file Excel sheet into Oracle Table
An alternative to this method through the use of Temp file:
Step1: Convert Excel sheet into .CSV file. For that you can use above “Save as” concept or you can use any third party tools.
Step2: Then use SQLloader load this file into temporary table.
Step3: Now simply write a stored procedure to take this data from temp table into the permanent table.
Method2: load data with external tables
Step1: Create a .CSV file of the Data in the excel sheet separated by ‘,’
Step2: Create a directory. For example:
SQL> CREATE DIRECTORY PRODUCT_DIR AS ‘D:\shahid\products’;
Step3: Create an external table in oracle which points to .CSV file
SQL>create table products_ext
(product_no number,description varchar2(100),
price varchar2(20)
)
organization EXTERNAL
(type oracle_loader
default directory PRODUCT_DIR
access parameters (records delimited by newline
badfile ‘products.bad’
logfile ‘products.log’
fields terminated by ‘,’
)
location (‘D:\shahid\products\products.csv’)
)
reject limit unlimited
/
Step 4: Once this table is created you can load the Data into the target directly
INSERT INTO products_ext (product_no, description, price);
SELECT * FROM products_ext;
Method3: load data through ODBC gateway
Step1: Setup ODBC gateway for the Excel file.
Step2: Now create an ODBC module corresponding to the ODBC gateway and invoke the import wizard to invoke the structure of excel sheet.
Step3: When its executed then oracle will read the data in excel through ODBC gateway into the target table.
An Alternative way to this method:
Step1: First of all we need to convert Excel file from .XLS format into .CSV or .text format so that OWB flat file can access it.
Step2: Now use the import option to import this CSV file.
Step3: Now Create SQL*Loader mappings to load data from CSV table to the staging tables
Step4: Finally a PL/SQL mapping is used to transfer these data from staging table to oracle table (final destination).
Method4: load data by code template or Oracle Heterogeneous services
In 11GR2, Code Template mapping (CT mapping) is introduced for heterogeneous data extraction, transformation and loading by using code templates. This enhances OWB to extract and load data (either with or without transformation) between different database platforms easily. Each CT mapping contains one or more execution units, and each unit is independent and associated with one code template. It utilizes the technology defined in the code template to move data, process data movement unit by unit, and then loads it into the final target. Because of the independency of each unit, CT Mapping allows users to handle data from different sources using different technologies.
Step1: Create CSV file from Excel file for that you can use any method, here simply open the file in MS-Excel and ‘SAVE AS’ with the Extension .CSV
Step2: Assign code template to an execution unit
For that just open the CT mapping and select the “Exection view” tab and then select the “Execution unit” box. Now select the appropriate code template from the drop down list (based on your data and functionality of template)
Step3: Browse or reference the CSV file or flat file through Import metadata wizard
Step4: Finally before executing check the proper path for csv file (default physical file location and check out your result.
CT mapping allows flexible technology for data movement, and the performance of large data movement depends on the code template applied. There may be some restriction. For details please visit the given link: http://docs.oracle.com/cd/E11882_01/owb.112/e10935/sap_km_mappings.htm
Note: If you are using TOAD then directly use TOAD Import function from the menu to directly import External file into the oracle Table and sane as above export table data into Excel file.
