Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 15 July 2012

How to Load Excel sheet into Oracle Table

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.

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.

11 comments:

  1. thanks for sharing.

    ReplyDelete
  2. If I've to import 500 million records into one table - for a performance test , which is the best method among the above?

    ReplyDelete
  3. hi i have to upload the excel file into temp tables using sqlloader
    excel file more than 3000 are there. please help me

    ReplyDelete
    Replies
    1. Di you try the concept already uploaded using Sql* loader and some other source.
      If you want more easy such "click and upload" like facility install the 'Toad' in your system.
      By using toad you can easily upload any huge excel file easily.

      Best Wishes
      Shahid Ahmed

      Delete
    2. i dont have TOdd, have client excel sheet now i connected the sql loader, sql developer there
      want to upload the 3000 excel data into temp tables
      ple help me

      Delete
  4. how to load excel files into temp tables using sql loader

    ReplyDelete
    Replies
    1. Dear Gopi,

      Try the following concept:
      1. Save as your excel file into .CSV file.
      2. Then make a control file usnig text editor by provoding loading information to SQL*loader.
      load data
      infile test.csv
      replace
      into table test_data
      fields terminated by ','
      3. Make sure the Test_data table with related structure is already created into oracle.
      4. Then finally use Sql*loader command from command prompt.
      sqlldr userid=hrms/hrms, control=control.ctl

      I already uploaded related documents on the blog. Try to search more information.
      I hope it will help you.

      With Best Wishes
      Shahid Ahmed

      Delete
  5. Hi shahid,
    Is there any way to load through all developer using procedure
    Thanks
    Soyeb

    ReplyDelete
  6. QUANTUM BINARY SIGNALS

    Professional trading signals sent to your mobile phone every day.

    Follow our trades NOW & profit up to 270% per day.

    ReplyDelete