How to load tab delimited file

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

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

-amar

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

What does your log file say ? Errors ??

-amar

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

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

Raghu

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

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

Raghu

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 ([email protected])
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

Quote:

---

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

I never actually tried it, but I found it in O'reilley's tip of the month category and if O'reilley put it there, I would say it works. But then again, nobody's perfect...

Hey I did not say that it will not work - I simply said that you can load a TAB delimited file w/o explicity specifying it in hex - it works if we specify "FIELDS TERMINATED BY WHITESPACE".

Sorry if I caused any confusion :(

-amar

Ahhhh, no big deal. I will also copy your example and save it with O'reilley's example. When the time comes for me to load a tab delimited file, I will now have 2 ways.

Thanks,
Joe

Thanks for your methods .. I will try them . At this point I have to load ASAP ,so I used Power Builder to load . It is GUI and works on TAB delimited file . But it is very very slow .. No choice ..


Raghu

I was just speaking to a friend about this and there is ANOTHER way it can be done, and he's used it this way.

FIELDS DELIMITED BY ' '

In between the quotes, he has tabbed over 1 time. That's pretty logical.

Thanks joe and Amar .. Both of your methods are working ..I tried terminated by whitespace as well as ' ' in between i tabed one time .. Both are working .. Next time my job will be faster since I am going to use SQL*LOADER with this idea's . it is taking forever from front end code reading and parsing the file ...

Raghu