Home » RDBMS Server » Server Utilities » update ASCII FIle
update ASCII FIle [message #115256] Fri, 08 April 2005 08:33 Go to next message
yidaki
Messages: 1
Registered: April 2005
Location: luxembourg
Junior Member
Hello,

i have a ascii (*.csv) File, wich was created by microsoft excel. i load the file into the database with the comfortable sqlldr in unix. now theres is a problem. the *.csv file contains some inkonsistent files, coused by a new create row. It looks like..

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

and it have to look like this


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

==> no rows that begins different like 'DE' OR 'INT'...

i hope you can help..

sorry for the bad taste of english...

thx

greetz

yid

Report message to a moderator

Re: update ASCII FIle [message #115532 is a reply to message #115256] Tue, 12 April 2005 09:08 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'd really need more information to have a chance at this one.

Report message to a moderator

Re: update ASCII FIle [message #115596 is a reply to message #115256] Tue, 12 April 2005 17:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
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>


Report message to a moderator

Re: update ASCII FIle [message #115598 is a reply to message #115256] Tue, 12 April 2005 17:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
Here is another solution that just involves fixing your ascii.csv file, by loading it into a temporary staging table, fixing the data in the table, then you can spool it back to your original file.

-- contents of test3.sql:
load data
infile 'ascii.csv'
into table temp_tab
fields terminated by x'10'
(all_in_one,
seq_col sequence)


-- temporary staging table:
scott@ORA92> create table temp_tab
  2    (all_in_one varchar2(4000),
  3  	seq_col    number)
  4  /

Table created.


-- load data into temporary staging table:
scott@ORA92> host sqlldr scott/tiger control=test3.ctl log=test3.log


-- fix data format:
scott@ORA92> update temp_tab o
  2  set    o.all_in_one = o.all_in_one ||
  3  	    (select i.all_in_one
  4  	     from   temp_tab i
  5  	     where  i.seq_col = o.seq_col + 1
  6  	     and    i.all_in_one not like 'INT%'
  7  	     and    i.all_in_one not like 'DE%')
  8  /

5 rows updated.

scott@ORA92> delete from temp_tab
  2  where  all_in_one not like 'INT%'
  3  and    all_in_one not like 'DE%'
  4  /

2 rows deleted.


-- data in corrected format:
scott@ORA92> select all_in_one from temp_tab
  2  /

ALL_IN_ONE
------------------------------------------------------------
DE;750;100;850;MNF;0.10;C;"Coba..H/Bieranschaut.";38364
INT;750;100;850;MNF;0.10;C;"Coba..H/Bier anschaut.";38365
DE;750;100;850;MNF;0.10;C;"Coba..H/Bieranschaut.";38366

scott@ORA92>


-- Then you can spool it back to the original file or other:
store set saved_settings replace
set echo off feedback off heading off pagesize 0 verify off
spool ascii.csv
select all_in_one from temp_tab;
spool off
start saved_settings


-- results in ascii.csv:
DE;750;100;850;MNF;0.10;C;"Coba..H/Bieranschaut.";38364
INT;750;100;850;MNF;0.10;C;"Coba..H/Bier anschaut.";38365
DE;750;100;850;MNF;0.10;C;"Coba..H/Bieranschaut.";38366


Then you can load it like a relugar file without the previous problems.

Report message to a moderator

Re: update ASCII FIle [message #115656 is a reply to message #115598] Wed, 13 April 2005 08:34 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Excellent!!!!

Report message to a moderator

Previous Topic: Referencing data from earlier lines or immediate previous line ...
Next Topic: SQL Loader, data load with Date and blank date column error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 12 16:03:48 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.