How to Import Xls Into Oracle

Written by barry kaye
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Import Xls Into Oracle
Load MS Excel data Into Oracle (upload buton image by Attila Toro from Fotolia.com)

Importing an .xls file into Oracle can be achieved with sqlldr.exe, a tool provided with the Oracle installation. Sqlldr stands for SQL Loader and it accepts a .csv file as an argument for importing into an Oracle table. MS Excel data is easily saved as .csv, so you can use this file in conjunction with a required custom control file to coordinate the import into the Oracle table. You must also have the target table present to import into to.

Skill level:
Moderate

Other People Are Reading

Things you need

  • File editor (ex: Notepad)
  • Microsoft Excel

Show MoreHide

Instructions

  1. 1

    Convert .xls to .csv. Open .xls in MS Excel. Delete any Column Headers. Go to "File, "Save As" and change "Type" to .csv format. Provide a new path and name and click "Save."

  2. 2

    Create the Control File. Open Notepad. Type the following and save as "...\filename.ctl."

    OPTIONS (ROWS=1, LOG='...\filename_load.log')
    
    LOAD DATA
    
    INFILE '...\filename.csv'
    
    INTO TABLE TABLENAME
    
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    
    TRAILING NULLCOLS
    
    (FIELD1, FIELD2, FIELD3, FIELDx TERMINATED BY WHITESPACE)
    

    Tablename is the name of your Oracle table, "....\" represented by the fully qualified path where your files reside, "filename_load.log" is the log file to confirm success or indicate processing errors and "FIELDS1...x" are the names of the .csv columns in order from left to right.

  3. 3

    Create an Oracle table, if necessary. At the Oracle SQL prompt, type or execute a file containing:

    CREATE TABLE TABLENAME(

    FIELD1 (plus field format ex: VARCHAR2(30),

    FIELD2 (plus field format ex: VARCHAR2(30),

    FIELD3 (plus field format ex: VARCHAR2(30),

    FIELDx (plus field format ex: VARCHAR2(30));

    "FIELDS" here must match in name and order the fields in the Step Two Control File. The control file will match each .csv column into it's corresponding Oracle table column.

  4. 4

    Execute the Sqlldr.exe. Find Sqlldr.exe. (Ex: C:\Oracle\product\10.1.0\Client_1\BIN\sqlldr.exe.) Open Notepad, type the path: .../sqlldr.exe user/password@service_name control='.../filename.ctl.'

    Save as filename.bat and double-click to execute.

    Notes:

    This process assumes your Oracle logon credentials are known (ex: user/password/service_name). The instructions passes to sqlldr.exe are contained in the Control File, as indicated in StepTwo.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.