| how to handle null values in sql loader [message #413592] |
Thu, 16 July 2009 10:37  |
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
|
Member |
|
|
Hi,
I am using sql loader to load data to tables from tab
delimeted files.
Here the problem is that the sql loader is not
handling null values.
If there is any null value in the flat file it is
moving the field values to left and loading to the table.
I am using NVL function to handle the null values,
but it is not working.
My control file is:
LOAD DATA
INFILE 'C:\tda_poc_files\SQL_scripts\Sourcefiles\TRADEGLOBNODE1.TXT'
BADFILE 'C:\tda_poc_files\SQL_scripts\Badfiles\TRADEGLOBNODE1.bad'
DISCARDFILE 'C:\C:\tda_poc_files\SQL_scripts\Discardfiles\TRADEGLOBNODE1.dsc'
TRUNCATE INTO TABLE stg_tradenode1 TRUNCATE
fields terminated by whitespace optionally enclosed by '"'
TRAILING NULLCOLS
(
TradeID "nvl(:TradeID,0)",
OrderID "nvl(:OrderID,0)",
CustID "nvl(:CustID, 0)",
OrderIndex "nvl(:OrderIndex ,0)",
StockSymName "nvl(:StockSymName,'')",
TradedVolume "nvl(:TradedVolume,0)",
TradedPrice char "nvl(:TradedPrice, '9999')",
tradedate "nvl(:tradedate ,0)",
tradetime "nvl(:tradetime ,0)",
TradeStatus "nvl(:TradeStatus,'')"
)
Source file is attached to this link
For the attached file in the first record, tradedate value
is coming in to tradeprice field
Hoping for a quick reply.
Thanks in Advance,
Ravi.
|
|
|
|
|
|
|
|
| Re: how to handle null values in sql loader [message #413658 is a reply to message #413594] |
Thu, 16 July 2009 23:43  |
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
|
Member |
|
|
Hi,
In the table date is varchar type.
this is the structure of the table.
TRADEID NUMBER(15)
ORDERID NUMBER(15)
CUSTID NUMBER(15)
ORDERINDEX NUMBER(2)
STOCKSYMNAME VARCHAR2(80)
TRADEDVOLUME NUMBER(20)
TRADEDPRICE VARCHAR2(20)
TRADEDATE VARCHAR2(15)
TRADETIME VARCHAR2(15)
TRADESTATUS VARCHAR2(10)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to handle null values in sql loader [message #413697 is a reply to message #413689] |
Fri, 17 July 2009 02:47 |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This is the table:SQL> desc stg_tradenode1;
Name Null? Type
----------------------------------------- -------- ----------------
TRADEID NUMBER(15)
ORDERID NUMBER(15)
CUSTID NUMBER(15)
ORDERINDEX NUMBER(2)
STOCKSYMNAME VARCHAR2(80)
TRADEDVOLUME NUMBER(20)
TRADEDPRICE VARCHAR2(20)
TRADEDATE VARCHAR2(15)
TRADETIME VARCHAR2(15)
TRADESTATUS VARCHAR2(10)
This is a control file; sample data is included. Note FIELDS TERMINATED BY '\t' line:LOAD DATA
INFILE *
replace INTO TABLE stg_tradenode1
FIELDS TERMINATED BY '\t'
trailing nullcols
(
TradeID,
OrderID,
CustID,
OrderIndex,
StockSymName,
TradedVolume,
TradedPrice,
tradedate,
tradetime,
TradeStatus
)
begindata
30001 20001 10000 1 TCS 18 07/16/2009 14:35:40 ACCEPTED
30002 20002 10000 1 ISP 10 12 07/16/2009 09:01:39 ACCEPTED
Loading session:C:\Temp>sqlldr scott/tiger@ora10 control=tctl.ctl log=tctl.log
SQL*Loader: Release 10.2.0.1.0 - Production on Pet Srp 17 09:43:58 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
C:\Temp>
Result:SQL> select * from stg_tradenode1;
TRADEID ORDERID CUSTID ORDERINDEX STOCKSYMNA TRADEDVOLUME TRADEDPRIC TRADEDATE TRADETIME TRADESTATU
-------- -------- ------- ---------- ---------- ------------ ---------- --------------- --------------- ----------
30001 20001 10000 1 TCS 18 07/16/2009 14:35:40 ACCEPTED
30002 20002 10000 1 ISP 10 12 07/16/2009 09:01:39 ACCEPTED
SQL> [Updated on: Fri, 17 July 2009 02:51] Report message to a moderator |
|
|
|
| Re: how to handle null values in sql loader [message #413736 is a reply to message #413697] |
Fri, 17 July 2009 07:27 |
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
|
Member |
|
|
Thanks a lot for the solution...
It worked..
finally we found that the problem is with the
"optionally enclosed by '"'".
If we remove this statment, its taking null values
and working fine.
I would like to know what is the use of this
statement in the control file.
Thanks again.
Ravi
|
|
|
|
|
|
| Re: how to handle null values in sql loader [message #413744 is a reply to message #413736] |
Fri, 17 July 2009 08:00 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Optnially Enclosed By is there for when you may have spaces in a single field and you used whitespace as your delimeter for example, or when you use a comma delimited file and you have commas embedded in your data. You would use the quotes around the field so the SQL*Loader will not split the data across fields.
You can look it up in the documentation.
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
