Home » RDBMS Server » Server Utilities » SQL*LOADER - Skipping Blank data records
SQL*LOADER - Skipping Blank data records [message #69255] Thu, 29 November 2001 10:00 Go to next message
oraboy
Messages: 97
Registered: October 2001
Member
Hi
Is there a way to specify in SQL*LOADER control file , not to load empty rows while upload

Say , the Flatfile looks like the following
a,b,c
,,
-> Blank line
z,x,y
,,
,,

I wish to upload the following rows alone
a,b,c
z,x,y

In my control file I have specified "Trailing Nullcols'
but still all rows are getting uploaded with blanks for all columns

Any suggestion would be appreciated

Regards
Venkat

----------------------------------------------------------------------
Re: SQL*LOADER - Skipping Blank data records [message #69259 is a reply to message #69255] Thu, 29 November 2001 11:15 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
by default, sqlloader discards all blank field records irrespective of you specify trailing nullcols or not.

----------------------------------------------------------------------
Re: SQL*LOADER - Skipping Blank data records [message #69263 is a reply to message #69255] Fri, 30 November 2001 04:19 Go to previous messageGo to next message
oraboy
Messages: 97
Registered: October 2001
Member
Yeah Thats what I expected too.
My excel sheet has like 520 rows (with values atleast for 1 field) but after loading into temp table the count(*) shows 673 rows

Well, Suresh is there a way to specify in control file that only records having certain columns as not null values should be loaded
(say my excel file has last 100 rows only with col1 value and rest blank..how to avoid uploading this?)

Appreciate your help
Oraboy

----------------------------------------------------------------------
Re: SQL*LOADER - Skipping Blank data records [message #69266 is a reply to message #69255] Fri, 30 November 2001 06:56 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
Look at following example:
my table structure : S VARCHAR2(10)
NUMBER(10,2)
R NUMBER

Control file:
---
LOAD DATA
INFILE 'new.TXT'
APPEND INTO TABLE new
when N <> X'0'
FIELDS TERMINATED BY ","
optionally enclosed by '"'
(s,n ,r)
----

in my control file i used when N<>X'0' , it means load will succeed only if value of N is not null. you can check for more than one column using AND operator

Data file data(New.txt):-
suresh,10.00,2
ddd,99.99,3
sam,,2
rob,,3
rose,,4
S,2,3

Invoking SQLLOADER

sqlldr username/password@cstring ldrnew.txt

table data:
S N R
---------- ---------- ----------
suresh 10 2
ram 99.99 3
S 2 3

you can see all rows with null values for N are discarded.

log file info:

Table NEW, loaded when N != 0X00(character '')
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
S FIRST * , O(") CHARACTER
N NEXT * , O(") CHARACTER
NULL if N = BLANKS
R NEXT * , O(") CHARACTER

Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.

Table NEW:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
3 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

HTH
SURESH

----------------------------------------------------------------------
Re: SQL*LOADER - Skipping Blank data records [message #70054 is a reply to message #69259] Sat, 06 April 2002 19:56 Go to previous message
nisha
Messages: 14
Registered: February 2002
Junior Member
hi,
the example stated above does not work for me if the datatype of N is character. Is it that it only works for number datatype
how do i skip blank char records?
Previous Topic: Re: FREE OCP PAPERS
Next Topic: Re: Moving Indexes from a tablespace to another - how?
Goto Forum:
  


Current Time: Sat Dec 03 23:55:53 CST 2022