Home » RDBMS Server » Server Utilities » sql loader problem: trailing nullcols
sql loader problem: trailing nullcols [message #143620] |
Fri, 21 October 2005 04:54 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
juicyapple
Messages: 92 Registered: October 2005
|
Member |
|
|
I try to load data in wmship2.txt to table ship_to, after loading success, I found that the field which should be null is filled with the next data, the command trailing nullcols seem does not work...
for example, the column addr4 is null but it is replace by the next column(state) and the state column is replace by the next column(region).
1000 SG. CHUA "LOT 1279, KM 1" JALAN SG. CHUA "43000, KAJANG SELANGOR, MY" SEL KR
load data
infile 'wmship2.txt'
badfile 'wmship.bad'
truncate
into table ship_to
fields terminated by '\t' optionally enclosed by '"'
trailing nullcols
(
cmpy constant "AA",
cmpyName constant "BB",
code1 constant "CC",
code2 constant "DD",
ship_to,
name,
addr1,
addr2,
addr3,
addr4,
state,
region
)
Can anyone shows me what mistake I have made?
Thanks.
|
|
|
Re: sql loader problem: trailing nullcols [message #143687 is a reply to message #143620] |
Fri, 21 October 2005 08:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Trailing nulls is for the end of a line, end of a record, not for in the middle of a record. If you have a missing field in your data then you still need to have an extra delimiter to account for it, to let oracle put in the null for that column.
I usually prefer delimiters that you can see, such as comma. You would then have back to back commas if you had a missing value.
|
|
|
Re: sql loader problem: trailing nullcols [message #143756 is a reply to message #143620] |
Fri, 21 October 2005 20:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
juicyapple
Messages: 92 Registered: October 2005
|
Member |
|
|
I have try to use comma as delimiter, but it return a lot of error message as below
Record 1: Rejected - Error on table SHIP_TO, column name.
no terminator found after TERMINATED and ENCLOSED field
Record 8: Rejected - Error on table SHIP_TO, column addr2.
no terminator found after TERMINATED and ENCLOSED field
...
but when I check the source file, the comma exist after each column...
line 1 in text file:
135144,ANSEL SDN BHD,"LOT 8A,",KULIM INDUSTRIAL ESTATE,"09000 KULIM, KEDAH, MY",04-4891033,KE,O
line 8:
128793,COMMECTS SDN BHD,"NO: 1,RU,JLN AU 5,",TAMAN LEMBAH KERAMAT,"54200, KUALA LUMPUR W., MY",03-41089618,KUL,KR
what does the message means actually??
Thanks.
|
|
|
Goto Forum:
Current Time: Sat Aug 24 08:37:02 CDT 2024
|