SQL Loader tab delimited files
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL Loader tab delimited files

  1. #1
    Join Date
    Feb 2002
    Posts
    2

    Angry

    I am trying to import selected columns from a file into one table. for example the data looks like this(Tab delimited)

    123456 34 23 Today Bank1
    126556 Today Bank2
    123478 34 23 Today Bank3
    193856 34 23 Today Bank4
    127265 34 23 Today Bank5
    098756 25 23 Today Bank6

    and the table is like this

    ID number(6);
    Day varchar2(5);
    Bank_name varchar2(12);

    I know I can load them in to a temporary table, then load what I want in to my actual table but is there a way I can do it without using a temporary table. The positions(character) are not allways the same so I can't use the positioning thing. But the positions are always the same with respect to the tabs. Even if the fields are empty the tabs are still there.

    Can anyone help?

    Thanks

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    LOAD DATA
    INFILE *
    APPEND (or insert/replace)
    INTO TABLE one_table
    FIELDS TERMINATED BY WHITESPACE
    (fld,fld2,fld3,fld4)
    BEGINDATA
    123456 34 23 Today Bank1
    126556 ?? ?? Today Bank2
    123478 34 23 Today Bank3
    193856 34 23 Today Bank4
    127265 34 23 Today Bank5
    098756 25 23 Today Bank6


  3. #3
    Join Date
    Dec 2000
    Posts
    126
    use SQLLDR with following control file

    OPTIONS(DIRECT=TRUE, ERRORS=999999)
    LOAD DATA
    CHARACTERSET WE8MSWIN1252
    INFILE '/u02/dwanat/datatfr/import/NSBGL'
    READBUFFERS 4
    TRUNCATE
    INTO TABLE dwstg_bgeog_loctn
    TRUNCATE
    REENABLE DISABLED_CONSTRAINTS
    FIELDS TERMINATED BY '\t'
    TRAILING NULLCOLS
    ( fld1,fld2,fld3,fld4,fld5)




    Note: '\t' = tab character

  4. #4
    Join Date
    Dec 2008
    Posts
    1

    tab delimited control file

    The script for loaidng tab delimited can be written as follows: It works good for me

    -- Loads data even though few fileds are null values.
    -- make sure the tempraray table columns data types length match with the raw data file
    -- NULLIF is used to let the field be null if a particular field has a null value
    -- x'09' is used for tab delimiting
    -- below are few sample coloumns
    -- a temprary table should be created in oracle with appropriate data types and length matching the tab delimted text field.

    LOAD DATA
    INFILE './xyz.txt'
    BADFILE './xyz.BAD'
    DISCARDFILE './xyz.DSC' DISCARDMAX 50
    INSERT INTO TABLE TEMP
    FIELDS TERMINATED BY x'09'
    TRAILING NULLCOLS
    ( Column1 NULLIF (Column1="NULL"),
    Column2 NULLIF (Column2="NULL"),
    Column3 NULLIF(Column3="NULL"),
    Column4 NULLIF (Column4="NULL"),
    Column5 "MM/DD/YY" NULLIF(Column5="NULL"),
    Column6 NULLIF(Column6="NULL"),
    )


    -- SQLLOAD COMMAND : sqlldr control=TEMPCONTROLFILE.ctl

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    6 years old! why bring up a 6 year old thread

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