How to load tab delimited file

[raghud]

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

[amar]

Try WHITESPACE ( in place of "," ..... etc.)

-amar

[raghud]

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

[amar]

What does your log file say ? Errors ??

-amar

[marist89]

You have to use:
delimited by X'99' << insert correct hex value for TAB character, I think 9

[raghud]

It hasn't created error log .. Even hex value 9 is not working ...

Raghu

[amar]

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

[raghud]

Thanks .. I will do some research why it's not working for me ...

Raghu

[jmr97]

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

[amar]

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