Home » RDBMS Server » Server Utilities » how to handle null values in sql loader (oracle 10g,windows xp sp3)  () 1 Vote
how to handle null values in sql loader [message #413592] Thu, 16 July 2009 10:37 Go to next message
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 #413594 is a reply to message #413592] Thu, 16 July 2009 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

You should use EXTERNAL TABLE instead of SQL*Loader
Re: how to handle null values in sql loader [message #413605 is a reply to message #413592] Thu, 16 July 2009 12:40 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
adusur wrote on Thu, 16 July 2009 11:37

tradedate "nvl(:tradedate ,0)",



How could you use NVL of a DATE and set it to 0; That is not a valid date.

You should be using
FIELDS TERMINATED BY x'09'



[Updated on: Thu, 16 July 2009 12:43]

Report message to a moderator

Re: how to handle null values in sql loader [message #413658 is a reply to message #413594] Thu, 16 July 2009 23:43 Go to previous messageGo to next message
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 #413659 is a reply to message #413605] Thu, 16 July 2009 23:44 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi,
Actually sql loader shifting the values left
and putting the null value in the lastr column.
Re: how to handle null values in sql loader [message #413661 is a reply to message #413592] Thu, 16 July 2009 23:47 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
>Actually sql loader shifting the values left and putting the null value in the lastr column.
Regardless, what is your solution?
Re: how to handle null values in sql loader [message #413662 is a reply to message #413661] Thu, 16 July 2009 23:49 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
I am asking for the solution
Re: how to handle null values in sql loader [message #413663 is a reply to message #413592] Thu, 16 July 2009 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
>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.
If a "tab" character really delimited fields, there would be no problem.

You have not provided any proof that every field is actually tab delimited.
Re: how to handle null values in sql loader [message #413669 is a reply to message #413663] Fri, 17 July 2009 00:19 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
I have attached the sample flat file to the post.
Check that.In that file one field is null.
I have pasted control file code also.
Re: how to handle null values in sql loader [message #413683 is a reply to message #413669] Fri, 17 July 2009 01:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
As joy_division already pointed out, you should be using:

FIELDS TERMINATED BY x'09'

not whitespace. x'09' is the hexadecimal representation of the tab key. If you used that then you would not have the column shift problem. When you use whitespace, it sees two tabs with no value inbetween as one whitespace and that causes your column shift.



Re: how to handle null values in sql loader [message #413689 is a reply to message #413683] Fri, 17 July 2009 01:20 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
I have tried x'09' also but it is also not working.
Re: how to handle null values in sql loader [message #413697 is a reply to message #413689] Fri, 17 July 2009 02:47 Go to previous messageGo to next message
Littlefoot
Messages: 19893
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
Sample data seems to be distorted; however, I just copied it from your example and pasted into my control file. In Notepad, there were two TABs.

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 Go to previous messageGo to next message
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 #413738 is a reply to message #413736] Fri, 17 July 2009 07:47 Go to previous messageGo to next message
Littlefoot
Messages: 19893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which one? "FIELDS TERMINATED BY '\t'"? '\t' represents <TAB>.
Re: how to handle null values in sql loader [message #413744 is a reply to message #413736] Fri, 17 July 2009 08:00 Go to previous messageGo to next message
joy_division
Messages: 4559
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.
Re: how to handle null values in sql loader [message #469524 is a reply to message #413744] Thu, 05 August 2010 01:51 Go to previous messageGo to next message
mastu
Messages: 3
Registered: August 2010
Location: Bangalore, India
Junior Member
Hi every body,

Thanks for the solution. Even i had the same issue and resolved after taking off Optionally enclosed by '"'

My question is what if the data contains " ?

mastu
Re: how to handle null values in sql loader [message #469542 is a reply to message #469524] Thu, 05 August 2010 02:07 Go to previous message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My question is what if the data contains " ?

Before or after taking off Optionally enclosed by '"'?

Regards
Michel
Previous Topic: Importing Dump From Higher Database Version 2 Lower.
Next Topic: Importing 10g dump in 11g with DATA_ONLY=Y in 11g and IGNORE = Y in 10g.
Goto Forum:
  


Current Time: Mon Dec 22 03:35:47 CST 2014

Total time taken to generate the page: 0.08490 seconds