Results 1 to 3 of 3

Thread: SQLLDR and TAB

  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: SQLLDR and TAB

    Hi
    I have at several files containing data terminated by TAB's. I have tried to make a .ctl file for the import, but there seems to be a problem when I get NULL-data.

    When I try to input data in field1, field2 and field3 and data looks like ABC null DEF, the data ends up like

    Field1: ABC
    Field2: DEF
    Field3:

    instead of

    Field1: ABC
    Field2:
    Field3: DEF

    My .ctl file (read <TAB> as tabulator)

    load data
    ...
    fields terminated by '<TAB>'
    optionally enclosed by '"'
    (Field1<TAB>Field2<TAB>Field3)

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    It should work as long as you entered <tab> as: ctl-v, ctl-i (or actually pressed the tab key):

    Code:
    LOAD DATA
    INFILE *
    INSERT INTO TABLE TEST1
    -- ------------------ v-- ctl-v, ctl-i or tab key here
    FIELDS TERMINATED BY "  " TRAILING NULLCOLS
    (col1
    ,col2
    ,col3
    ,col4
    )
    BEGINDATA
    a01     b01     cc1     d1d
    a01     b02             d2d
    a02     b01     cc3     d3d
    a02             cc4     d4d
    a03     b01     cc5
    a03     b02     cc6     d6d
    $ sqlldr scott/tiger control=test1.ctl log=test1.log

    SQL*Loader: Release 8.1.7.4.0 - Production on Thu Feb 9 11:46:32 2006

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    Commit point reached - logical record count 6
    $ sqlplus scott/tiger

    SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 9 11:46:44 2006

    (c) Copyright 2000 Oracle Corporation. All rights reserved.


    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.4.0 - Production

    Code:
    SQL>select * from test1;
    
    COL1       COL2       COL3       COL4
    ---------- ---------- ---------- ----------
    a01        b01        cc1        d1d
    a01        b02                   d2d
    a02        b01        cc3        d3d
    a02                   cc4        d4d
    a03        b01        cc5
    a03        b02        cc6        d6d
    
    6 rows selected.


    Last edited by LKBrwn_DBA; 02-09-06 at 12:57.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    for TABS I use this line:
    PHP Code:
    FIELDS TERMINATED BY '\t' 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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