| SQL*LOADER - Skipping Blank data records [message #69255] |
Thu, 29 November 2001 10:00  |
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 #69263 is a reply to message #69255] |
Fri, 30 November 2001 04:19  |
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 |
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
----------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
