DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: How to load tab delimited file

  1. #1
    Join Date
    Aug 2001
    Posts
    267
    I need to load tab delimited file through loader . I know how to load ',' delimited . In the control file we use fields terminated by "," ,if spaces fields terminated by spaces . Question is How to do for TAB delimited file ..I can't go by position since it is not fixed length record file .. Need help on this ,Thanks in advance ..


    Raghu
    Raghu

  2. #2
    Join Date
    Mar 2001
    Posts
    314
    Try WHITESPACE ( in place of "," ..... etc.)

    -amar

  3. #3
    Join Date
    Aug 2001
    Posts
    267
    I tried ,but I couldn't get . I am getting Commit point reached ,but it is writing all the records to bad file . When I checked the table it is empty ..Any idea ..

    Raghu
    Raghu

  4. #4
    Join Date
    Mar 2001
    Posts
    314
    What does your log file say ? Errors ??

    -amar

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You have to use:
    delimited by X'99' << insert correct hex value for TAB character, I think 9
    Jeff Hunter

  6. #6
    Join Date
    Aug 2001
    Posts
    267
    It hasn't created error log .. Even hex value 9 is not working ...

    Raghu
    Raghu

  7. #7
    Join Date
    Mar 2001
    Posts
    314
    This works for me!

    create table tt ( c1 number, c2 varchar2(10));

    contents of the data file (TAB delimited)
    raghu.txt
    1 first
    2 second
    3 third
    4 fourth
    5 fifth


    control file
    raghu.ctl
    LOAD DATA
    INFILE 'c:\tmp\raghu.txt'
    APPEND
    INTO TABLE tt
    FIELDS TERMINATED BY WHITESPACE
    ( c1,c2)

    load data:

    C:\tmp>sqlldr80 amar/amar@demo raghu

    SQL*Loader: Release 8.0.5.0.0 - Production on Tue Aug 28 10:06:41 2001

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

    Commit point reached - logical record count 5

    C:\tmp>

    SQL> select * from tt:
    C1 C2
    -------- --------------
    1 first
    2 second
    3 third
    4 fourth
    5 fifth


    -amar


  8. #8
    Join Date
    Aug 2001
    Posts
    267
    Thanks .. I will do some research why it's not working for me ...

    Raghu
    Raghu

  9. #9
    Join Date
    Aug 2001
    Posts
    5
    Just throwing in my 2 cents. I saved this but I'm unsure where I found it. This may help you also.

    December's Tip of the Month
    Using SQL*Loader to Load Tab-delimited Files
    Tip supplied by: Eva Blinder (eblinder@hotmail.com)
    The trick is that the tab delimiter has to be specified in hexadecimal format, and SQL*Loader is very picky about the syntax. Here is an example of the control file you would create to specify a delimiter of tab:
    LOAD DATA

    INFILE 'FILENAME.TXT'

    INTO TABLE t1

    FIELDS DELIMITED BY x'09'

    TRAILING NULLCOLS

    ( column 1....

    )

    Good luck,
    Joe

  10. #10
    Join Date
    Mar 2001
    Posts
    314
    Originally posted by jmr97

    The trick is that the tab delimiter has to be specified in hexadecimal format, ..........
    Joe

    This is simply NOT true.

    -amar

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