| How to repeat columns [message #74629] |
Mon, 24 January 2005 09:37  |
Leonardo Mattos
Messages: 4
Registered: January 2005
|
Junior Member |
|
|
Hi,
Suppose you have a file where the columns doesn´t have fixed length. They are separated by ';'. Each line contains three columns. I want to load these columns to table that have four columns. The first column of the table will be filled by the first column of the file, the second and third columns of table will be filled by the second column of the file and the last column of the table will be filled by the last column of the file.
The layout of control file is the following:
LOAD DATA INFILE 'abc.txt' INTO TABLE ABC
APPEND
FIELDS TERMINATED BY ';'
(
COLUMN1,
COLUMN2,
COLUMN3 :COLUMN2,
COLUMN4
)
The problem is that the log file says that COLUMN4 is missing and tell me to use TRAILING NULLCOLS. But it´s not what i want. How can i make COLUMN4 read the value of the third column of file?
I can´t be more specific cause i´m building an interface on my application where the user can use SQL*Loader to load data from a file. I just don´t want to restrict the user options.
Thanks
|
|
|
|
| Re: How to repeat columns [message #74630 is a reply to message #74629] |
Mon, 24 January 2005 10:35  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your columns need to be in the order read, that is column1, column2, column4, then any columns that are calculated based on other columns, column3 ":column2" need to be at the bottom with double quotes around the referenced column. You probably still need the trailing nullcols and even if you don't it won't hurt. Please try the suggested control file below.
LOAD DATA INFILE 'abc.txt' INTO TABLE ABC
APPEND
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
COLUMN1,
COLUMN2,
COLUMN4,
COLUMN3 ":COLUMN2"
)
I used the above as test.ctl, with abc.txt containing:
col1;col2;col3
value1;value2;value3
and ran the following and got the results shown:
scott@ORA92> create table abc
2 (column1 varchar2(10),
3 column2 varchar2(10),
4 column3 varchar2(10),
5 column4 varchar2(10))
6 /
Table created.
scott@ORA92> host sqlldr scott/tiger control=test.ctl log=test.log
scott@ORA92> select * from abc
2 /
COLUMN1 COLUMN2 COLUMN3 COLUMN4
---------- ---------- ---------- ----------
col1 col2 col2 col3
value1 value2 value2 value3
|
|
|
|
|
|
| Re: How to repeat columns [message #74637 is a reply to message #74634] |
Mon, 24 January 2005 20:32 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
You could put not null constraints on the columns or you could just load the data with column2, then do a separate update to set column3 = column2 after the load.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
