|
|
|
|
|
|
| Re: Msg for Sujit [message #71963 is a reply to message #71961] |
Tue, 11 February 2003 02:22   |
sujit
Messages: 94
Registered: April 2002
|
Member |
|
|
LOAD DATA
INFILE *
INSERT INTO TABLE TABLE_NAME
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS
(
FIELD1,
FIELD2,
...
)
NOTE: The X'09' should be in uppercase.
Hope that clarifies ur doubt.
|
|
|
|
| Re: Msg for Sujit [message #71969 is a reply to message #71961] |
Tue, 11 February 2003 11:30 |
Swamy
Messages: 78
Registered: June 2002
|
Member |
|
|
Hi,
I can load Tab delimited file in to a table by using X’09’ syntax. My problem is, I have null data for fields ATTRIBUTE_4 and ATTRIBUTE_5, but the result is shifting the data of columns 6,7 to columns 4,5. Even, I changed the syntax as advised in this forum. Please help me. The below is my control file syntax.
LOAD DATA
TRUNCATE
INTO TABLE TEMP_PAYERS_dupe
fields terminated by X'09' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
ATTRIBUTE_1 CHAR "UPPER(TRIM(:ATTRIBUTE_1))",
ATTRIBUTE_2 CHAR "UPPER(TRIM(:ATTRIBUTE_2))",
ATTRIBUTE_3 CHAR "UPPER(TRIM(:ATTRIBUTE_3))",
ATTRIBUTE_4 CHAR nullif ATTRIBUTE_4="(null)" "UPPER(TRIM(:ATTRIBUTE_4))",
ATTRIBUTE_5 CHAR nullif ATTRIBUTE_5="(null)" "UPPER(TRIM(:ATTRIBUTE_5))",
ATTRIBUTE_6 CHAR "UPPER(TRIM(:ATTRIBUTE_6))",
ATTRIBUTE_7 CHAR "UPPER(TRIM(:ATTRIBUTE_7))",
ATTRIBUTE_8 CHAR "UPPER(TRIM(:ATTRIBUTE_8))",
ATTRIBUTE_9 CHAR "UPPER(TRIM(:ATTRIBUTE_9))",
ATTRIBUTE_10 CHAR "UPPER(TRIM(:ATTRIBUTE_10))",
ATTRIBUTE_11 CHAR "UPPER(TRIM(:ATTRIBUTE_11))",
ATTRIBUTE_12 CHAR "UPPER(TRIM(:ATTRIBUTE_12))",
ATTRIBUTE_13 CHAR "UPPER(TRIM(:ATTRIBUTE_13))",
ATTRIBUTE_14 CHAR "UPPER(TRIM(:ATTRIBUTE_14))",
ATTRIBUTE_15 CHAR "UPPER(TRIM(:ATTRIBUTE_15))",
SEQ_NUM "TEMP_PAYER.NEXTVAL"
)
|
|
|
|
| Re: Msg for Sujit [message #71972 is a reply to message #71969] |
Tue, 11 February 2003 21:14 |
sujit
Messages: 94
Registered: April 2002
|
Member |
|
|
Hi Swamy,
I am afraid the data file is not the way it should be
when there are NULLs in some attributes. Actually if the scene is like this:
ATTRIB_4 AND ATTRIB_5 Are NULLs then the data in that
row should come as:
..(ATTRIB_3)TAB()TAB()TAB(ATTRIB_6)...
(Assume there is no bracket there, but for understanding.)
So the blank brackets () suggest that between TABs there
are NULL fields. It can not be that you should miss some
TABs altogether and that the fields succeeding it viz.,
ATTRIB_6, ATTRIB_7 take the position of ATTRIB_4 and
ATTRIB_5 respectively.
So I would suggest there is something wrong with the
data file.
And also, why would u need to make NULLIF for NULL
data?
It will anyhow be taken as NULLs.
Hope that works fine.
And will you please remove "Msg for Sujit" from the subject?
And put something different (and appropriate. But better
to keep the "Re:" in the beginning)
Please please please. ;-)
Sujit
|
|
|
|
| Re: How to import tab seperated file [message #72702 is a reply to message #71960] |
Tue, 23 September 2003 23:00 |
Steve
Messages: 190
Registered: September 1999
|
Senior Member |
|
|
I am having a similar problem to the person above. My tab delimited file has null columns. If I open the file in Excel and resave as comma delimited, sqlldr works just fine (with the ',' field terminator). However, if I leave the file in tab delimited form and use the X'09' field terminatior, sqlldr reads the data into the wrong columns.
exerpt of my control file is
options (silent=(header,feedback), rows=1000)
LOAD DATA
INFILE 'F:filesreal_estateworknormlscoded.txt'
BADFILE 'F:filesreal_estateworknormlsnormls_data.bad'
DISCARDFILE 'F:filesreal_estateworknormlsnormls_data.dsc'
REPLACE
INTO TABLE kp_normls_data
FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
