Home » RDBMS Server » Server Utilities » update ASCII FIle
|
|
|
|
| Re: update ASCII FIle [message #115596 is a reply to message #115256] |
Tue, 12 April 2005 17:04   |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It sure would be a whole lot easier if you could get your ascii csv file in an easier format. Perhaps there is some way that you can increase the line length when creating it. Otherwise, here is a roundabout solution. You can use continueif to specify that you should continue the record with the data on the next line if one condition is not met. For example, you could continue if the first value of the next line != 'DEF'. However, you can only specify one such condition. But, you could put DEF in one control file and INT in another control file and do two separate runs. However, there would be two other problems. One problem is that it would be trying to concatenate the new records starting with DEF to the last column in one run and the new records starting with INT to the last column in the other run. To correct this, you could use a function, to extract just the numeric portion. The other problem is that it would cause duplicate inserts. However, if there is a unique key on the table, then these would just be rejected. Please see the example below.
-- contents of ascii.csv:
DE;750;100;850;MNF;0.10;C;"Coba..H/Bier
anschaut.";38364
INT;750;100;850;MNF;0.10;C;"Coba..H/Bier anschaut.";38365
DE;750;100;850;MNF;0.10;C;"Coba..H/Bier
anschaut.";38366
-- contents of test.ctl:
load data
infile 'ascii.csv'
APPEND
CONTINUEIF NEXT PRESERVE (1) != 'INT'
into table your_table
fields terminated by ';'
trailing nullcols
(col1, col2, col3, col4, col5, col6, col7, col8,
COL9 "YOUR_FUNC (:COL9)")
-- contents of test2.ctl:
load data
infile 'ascii.csv'
APPEND
CONTINUEIF NEXT PRESERVE (1) != 'DE'
into table your_table
fields terminated by ';'
trailing nullcols
(col1, col2, col3, col4, col5, col6, col7, col8,
COL9 "YOUR_FUNC (:COL9)")
-- table with unique key:
scott@ORA92> create table your_table
2 (col1 varchar2(4),
3 col2 number,
4 col3 number,
5 col4 number,
6 col5 varchar2(4),
7 col6 number,
8 col7 varchar2(4),
9 col8 varchar2(30),
10 col9 number UNIQUE)
11 /
Table created.
-- function to extract numeric portion from start of string:
scott@ORA92> create or replace function your_func
2 (p_string in varchar2)
3 return number
4 as
5 v_string varchar2(4000);
6 begin
7 for i in 1 .. length (p_string) loop
8 if substr (p_string, i, 1) in
9 ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
10 then v_string := v_string || substr (p_string, i, 1);
11 else exit;
12 end if;
13 end loop;
14 return to_number (v_string);
15 end your_func;
16 /
Function created.
scott@ORA92> show errors
No errors.
-- load data:
scott@ORA92> host sqlldr scott/tiger control=test.ctl log=test.log
scott@ORA92> host sqlldr scott/tiger control=test2.ctl log=test2.log
-- results:
scott@ORA92> select * from your_table
2 /
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9
---- ---------- ---------- ---------- ---- ---------- ---- ------------------------------ ----------
DE 750 100 850 MNF .1 C "Coba..H/Bieranschaut." 38364
INT 750 100 850 MNF .1 C "Coba..H/Bier anschaut." 38365
DE 750 100 850 MNF .1 C "Coba..H/Bieranschaut." 38366
scott@ORA92>
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jul 03 08:31:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
