OraFAQ Forum: Server Utilities » SQL*Loader: load tab-delimited file with NULL values

Home » RDBMS Server » Server Utilities » SQL*Loader: load tab-delimited file with NULL values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Loader: load tab-delimited file with NULL values [message #70578]

Tue, 02 July 2002 11:49

George
Messages: 68
Registered: April 2001

Member

Hi,

I want to upload the tab-delimited file into Oracle. I've made it a lot of time before for CSV files and I didn't have any problem with loading files even if there some NULL values inside (not in the end of the record which is handled by TRAILING NULLCOLS). Now I have to load tab-delimited file with NULL value in mid's columns. I can do that. I made the test example:

OPTIONS (ROWS = 1000, SILENT = ALL)
load data
infile *
APPEND
into table test
fields terminated by X'09' optionally enclosed by '"'
TRAILING NULLCOLS
(col1,
col2,
col3,
col4 DATE "MM/DD/YY HH24:MI:SS")
BEGINDATA
1 15.00 "12/23/02 0:00"
2 ";slkfdgj;sdlf" 44.33 12/21/02 13:45:00
3 gonegone 138.00 12/23/02 0:00

The second and the third lines are loaded without any problems. But the first one is discarded because of after "1" there are two tabs. Loader reads them like one tab and pushes 15.00 to col2 but not col2 as it is supposed to do.
Any help is appreciated
George

Report message to a moderator

Re: SQL*Loader: load tab-delimited file with NULL values [message #70579 is a reply to message #70578]

Tue, 02 July 2002 16:33

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you can use the following syntax with necessary changes.

i am assuming that col1 may have nulls and datatype as char(10)

OPTIONS (ROWS = 1000, SILENT = ALL)
load data
infile *
APPEND
into table test
fields terminated by X'09' optionally enclosed by '"'
TRAILING NULLCOLS
(
col1 char(10) nullif col1="(null)",
col2,
col3,
col4 DATE "MM/DD/YY HH24:MI:SS")
BEGINDATA
1 15.00 "12/23/02 0:00"
2 ";slkfdgj;sdlf" 44.33 12/21/02 13:45:00
3 gonegone 138.00 12/23/02 0:00

Report message to a moderator

Re: SQL*Loader: load tab-delimited file with NULL values [message #73224 is a reply to message #70578]

Fri, 05 March 2004 01:36

Aldo Valerio
Messages: 7
Registered: March 2004

Junior Member

Did you solve this problem? I tried Mahesh's idea, but I still get the same error.

Report message to a moderator

Re: SQL*Loader: load tab-delimited file with NULL values [message #73228 is a reply to message #70578]

Fri, 05 March 2004 02:47

Aldo Valerio
Messages: 7
Registered: March 2004

Junior Member

Barbara Boehmer (in this same forum) found a solution that works for me. Remove the OPTIONALLY ENCLOSED BY '"'. I didn't have quotes in my tab-delimited data so it was ok.

Report message to a moderator

Previous Topic:	Urgent – SQL Loader problem
Next Topic:	DUPLICATE Failure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 11 15:02:19 CST 2025