Export Comma Delimited File to Oracle
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Export Comma Delimited File to Oracle

  1. #1
    Join Date
    Mar 2002
    Posts
    38

    Export Comma Delimited File to Oracle

    hi friends,

    I am having a problem regarding the export of comma delimited flat files to Oracle database by using the SQL*Loader.Please give me your suggestions/solutions regarding this.

    My Table name :Temp
    Table Structure:
    Name Char
    Address Char
    State Char
    City Char
    Zip Char
    Country Char
    Phone Number


    My data in the flat file is (Row wise)
    "King","Park Street","CA","California",234444,"US",4234244
    "James",Lincoln Street","SA",4535355
    "Ronald",Church Avenue","AUS",534533555

    Now how I need to export these data into Oracle.In the above 3 records some of the data is missing for some columns and also there is no comma delimited for those columns.Assume that I am having one million records to export into Oracle in n number of files.How can I do this?
    If you can give the script or explain me the method it will be more useful for me.

    Thanks in Advance

    Suroop
    SUROOP B

  2. #2
    Join Date
    Feb 2004
    Posts
    77
    The datafile format needs to change to a standard format. Have a null (,,) for all the values that are not present in the record.

  3. #3
    Join Date
    Jun 2003
    Posts
    132
    Try external tables.

    http://www.databasejournal.com/featu...e.phpr/1581021

    Regards

    Roman

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Yeah, you're screwed here ... if you can't get the source system to send you decent data, then you'll have to write some PL/SQL to work out which data element is which.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by slimdave
    Yeah, you're screwed here ... if you can't get the source system to send you decent data, then you'll have to write some PL/SQL to work out which data element is which.
    utl_file is fun to play around with to read and process the file in pl/sql

    good luck!

    steve
    I'm stmontgo and I approve of this message

  6. #6
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    Why dont you use external tables. They are much hassle free. Assuming that you are using oracle 9i, you could try this:
    Code:
    create or replace directory data_dir as '/tmp'
     /
    
    
    CREATE TABLE EXTERNAL_TABLE
    (
    NAME VARCHAR2(30),
    ADDRESS VARCHAR2(50),
    STATE VARCHAR2(50),
    CITY VARCHAR2(50),
    ZIP VARCHAR2(20),
    COUNTRY VARCHAR2(40)
    PHONE NUMBER(15)
    
    )
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY DATA_DIR
         ACCESS PARAMETERS 
           ( records delimited by newline
     fields terminated by ','
     missing field values are null
     (NAME,ADDRESS,STATE,CITY,ZIP,COUNTRY,PHONE)
         )
         LOCATION (DATA_DIR:'file.csv')
      )
    REJECT LIMIT UNLIMITED;

  7. #7
    Join Date
    Aug 2002
    Posts
    115

    Re: Export Comma Delimited File to Oracle

    "King","Park Street","CA","California",234444,"US",4234244
    "James",Lincoln Street","SA",4535355
    "Ronald",Church Avenue","AUS",534533555

    Now 4535355..its zip code or tel no..."SA"..its state,Country??

    I think its going to be very difficult..even using UTL_FILE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width