jyhuang
asked on
SQL Loader - Tab delimited
I want to use sql loader to load a tab delimited files. How do I specify "TAB" in the sql loader control file? What do I put in the "???" in the line below?
FIELDS TERMINATED BY "???" OPTIONALLY ENCLOSED BY '"'
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hope this helps!!
LOAD DATA
INFILE 'files.txt'
INTO TABLE files
INSERT
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
(ID_FILE,
NATION,
PATENT,
TITLE,
HKEY)
Another Eg:
LOAD DATA
INFILE 'FILENAME.TXT'
INTO TABLE t1
FIELDS DELIMITED BY x'09'
TRAILING NULLCOLS
( column 1....
)
LOAD DATA
INFILE 'files.txt'
INTO TABLE files
INSERT
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
(ID_FILE,
NATION,
PATENT,
TITLE,
HKEY)
Another Eg:
LOAD DATA
INFILE 'FILENAME.TXT'
INTO TABLE t1
FIELDS DELIMITED BY x'09'
TRAILING NULLCOLS
( column 1....
)
Since "chr(09)" works to insert a tab character, it may work in SQL*Loader as well (but I haven't tried it) like this:
FIELDS TERMINATED BY chr(09)
FIELDS TERMINATED BY chr(09)
Also add after each column
(column_name1 datatype TERMINATED BY X'09',
column_name2 datatype TERMINATED BY X'09')
Gio
(column_name1 datatype TERMINATED BY X'09',
column_name2 datatype TERMINATED BY X'09')
Gio
ASKER
Thank you all for your responses. Almost all your suggestions are working. I can use space, tab, or X'09'. But since myerpz is the first one who gave the correct answer, I am giving the points to him/her.
FIELDS TERMINATED BY WHITESPACE
FIELDS TERMINATED BY ' '
where the space between the quotes is a Tab
Good luck