OraFAQ Forum: SQL & PL/SQL » UTL File reader

Home » SQL & PL/SQL » SQL & PL/SQL » UTL File reader (Oracle,10G,Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UTL File reader [message #636270]

Mon, 20 April 2015 12:44

gsaravanan
Messages: 16
Registered: April 2015
Location: chennai

Junior Member

Hi Friends,
Please help me to solve for my below logic requirement. As i have one text file which has some details (like columns)and i want to read this text file by using utl file reader and have to dispaly as rows.

My text file as like below.

:20:STARTUM
:25:12768/38790
:26C:478878
:30:TRY4678979/3989
:61:1234
:86:98076/0000
:61:2222
:86:78976/00011
:67F:EGYRTHUH

Now my output should be 2 rows as below
:20:STARTUM:25:12768/38790:26C:478878:30:TRY4678979/3989:61:1234:86:98076/0000:67F:EGYRTHUH
:20:STARTUM:25:12768/38790:26C:478878:30:TRY4678979/3989:61:2222:86:78976/00011:67F:EGYRTHUH

In my input text file have :61,:86 as two times ,so first time in first row should have one :61,:86 and reming things and all common for two rows. And in second row should have remaining :61,:86 with common details ..

Please help to get resolve this asap.

Report message to a moderator

Re: UTL File reader [message #636273 is a reply to message #636270]

Mon, 20 April 2015 12:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: UTL File reader [message #636275 is a reply to message #636273]

Mon, 20 April 2015 12:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Please help to get resolve this asap.

text as input & text as output; therefore why is Oracle part of any solution?
When your only tool is a hammer, all problems are viewed as nails.
Solution can be obtained using any procedural language; like JAVA, PERL, etc.

Report message to a moderator

Re: UTL File reader [message #636276 is a reply to message #636270]

Mon, 20 April 2015 12:56

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Can you explain what you mean by Quote:

i want to read this text file by using utl file reader

it is not a term with which I am familiar. Also, I do not understand the rules for the transformation, or what you intend to do with the result.
I dont think anyone can help with out more information. asap.

Report message to a moderator

Re: UTL File reader [message #636280 is a reply to message #636270]

Mon, 20 April 2015 13:08

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What should be the result if the file is:

:20:STARTUM
:25:12768/38790
:26C:478878
:30:TRY4678979/3989
:26C:123456
:30:ABC1234567/1234
:61:1234
:86:98076/0000
:61:2222
:86:78976/00011
:67F:EGYRTHUH

Report message to a moderator

Re: UTL File reader [message #636292 is a reply to message #636280]

Mon, 20 April 2015 21:02

gsaravanan
Messages: 16
Registered: April 2015
Location: chennai

Junior Member

Hi Michel,
Thank you, as i have 11 columns ,in this :61 and :86 coming two times, now my result display as row wise and :61,:86 should come once for each row and should not repeat.

Please find my result as below,
(Below result have 2 rows)
:20:STARTUM:25:12768/38790:26C:478878:30:TRY4678979/3989:61:1234:86:98076/0000:67F:EGYRTHUH

:20:STARTUM:25:12768/38790:26C:478878:30:TRY4678979/3989:61:2222:86:78976/00011:67F:EGYRTHUH

Report message to a moderator

Re: UTL File reader [message #636293 is a reply to message #636276]

Mon, 20 April 2015 21:09

gsaravanan
Messages: 16
Registered: April 2015
Location: chennai

Junior Member

Hi John,
Thank you, say i have one text file which has 11 columns and I want to display as row wise, here my text file have personal details for one person and :61,86 have different value where remaining columns have common details.So I want to read this text file and display as 2 rows.each row should have one :61,86

Report message to a moderator

Re: UTL File reader [message #636295 is a reply to message #636293]

Mon, 20 April 2015 21:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I want to display as row wise,

Explain why Oracle RDBMS is involved.

Report message to a moderator

Re: UTL File reader [message #636297 is a reply to message #636270]

Mon, 20 April 2015 22:59

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl> HOST TYPE c:\my_oracle_files\input.dat
:20:STARTUM
:25:12768/38790
:26C:478878
:30:TRY4678979/3989
:61:1234
:86:98076/0000
:61:2222
:86:78976/00011
:67F:EGYRTHUH

SCOTT@orcl> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl> DECLARE
  2    v_in_file          UTL_FILE.FILE_TYPE;
  3    v_in_line          VARCHAR2(32767);
  4    v_count         NUMBER := 0;
  5    v_out_line1        VARCHAR2(32767);
  6    v_out_line2        VARCHAR2(32767);
  7    v_out_file         UTL_FILE.FILE_TYPE;
  8  BEGIN
  9    -- read data:
 10    v_in_file := UTL_FILE.FOPEN ('MY_DIR', 'input.dat', 'R');
 11    LOOP
 12      BEGIN
 13        UTL_FILE.GET_LINE (v_in_file, v_in_line, 32767);
 14        IF SUBSTR (v_in_line, 2, 2) != '61' AND SUBSTR (v_in_line, 2, 2) != '86' THEN
 15          v_out_line1 := v_out_line1 || v_in_line;
 16          v_out_line2 := v_out_line2 || v_in_line;
 17        ELSIF (SUBSTR (v_in_line, 2, 2) = '61' OR SUBSTR (v_in_line, 2, 2) = '86') AND v_count < 2 THEN
 18          v_out_line1 := v_out_line1 || v_in_line;
 19          v_count := v_count + 1;
 20        ELSIF (SUBSTR (v_in_line, 2, 2) = '61' OR SUBSTR (v_in_line, 2, 2) = '86') AND v_count = 2 THEN
 21          v_out_line2 := v_out_line2 || v_in_line;
 22        END IF;
 23      EXCEPTION
 24        WHEN NO_DATA_FOUND THEN
 25          EXIT;
 26      END;
 27    END LOOP;
 28    UTL_FILE.FCLOSE (v_in_file);
 29    -- output data:
 30    v_out_file := UTL_FILE.FOPEN ('MY_DIR', 'output.dat', 'W');
 31    UTL_FILE.PUT_LINE (v_out_file, v_out_line1);
 32    UTL_FILE.PUT_LINE (v_out_file, v_out_line2);
 33    UTL_FILE.FCLOSE (v_out_file);
 34  END;
 35  /

PL/SQL procedure successfully completed.

SCOTT@orcl> HOST TYPE output.dat
:20:STARTUM:25:12768/38790:26C:478878:30:TRY4678979/3989:61:1234:86:98076/0000:67F:EGYRTHUH
:20:STARTUM:25:12768/38790:26C:478878:30:TRY4678979/3989:61:2222:86:78976/00011:67F:EGYRTHUH

SCOTT@orcl>

Report message to a moderator

Re: UTL File reader [message #636299 is a reply to message #636292]

Tue, 21 April 2015 00:25

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

gsaravanan wrote on Tue, 21 April 2015 04:02

Hi Michel,
Thank you, as i have 11 columns ,in this :61 and :86 coming two times, now my result display as row wise and :61,:86 should come once for each row and should not repeat.

Please find my result as below,
(Below result have 2 rows)
:20:STARTUM:25:12768/38790:26C:478878:30:TRY4678979/3989:61:1234:86:98076/0000:67F:EGYRTHUH

:20:STARTUM:25:12768/38790:26C:478878:30:TRY4678979/3989:61:2222:86:78976/00011:67F:EGYRTHUH

This does not answer my question.
What should be the result for the file I give?

Or define in details what could be the file.
Are there ALWAYS 9 lines?
Do they ALWAYS start with the same prefix (before first Smile

Smile

that what you showed?
ALWAYS in the same order?
...

Report message to a moderator

Re: UTL File reader [message #636302 is a reply to message #636297]

Tue, 21 April 2015 00:52

gsaravanan
Messages: 16
Registered: April 2015
Location: chennai

Junior Member

Hi Thanks a lot for your guide ,
if i have only one set input file then its working fine,say my input text file have N number of datas then how i have to proceed further like this.

my input text file like below.

:20:STARTUMSE
:25:10080000/1008546EXT
:28C:00002/001
:60F:C150409EUR51,12
:61:1312031203CR25,56N087NONREF
:86:?008JAYASEELAN12345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:61:1401030103CR25,56N087NONREF
:86:?008JAYASEELAN12345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:62F:C150409EUR0,00

:20:STARTUMSE
:25:10080000/1009010EXT
:28C:00002/001
:60F:C150409EUR40,00
:61:1312171217CR20,00N087NONREF
:86:?008JAYASEELAN12345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:61:1401160116CR20,00N087NONREF
:86:?008JAYASEELAN12345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:62F:C150409EUR0,00

:20:STARTUMSE
:25:10080000/1009347EXT
:28C:00002/001
:60F:C150409EUR30,00
:61:1312031203CR15,00N087NONREF
:86:?008JAYASEELAN12345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:61:1401030103CR15,00N087NONREF
:86:?008JAYASEELAN12345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:62F:C150409EUR0,00

please let me know how to proceed with this.

Report message to a moderator

Re: UTL File reader [message #636303 is a reply to message #636299]

Tue, 21 April 2015 01:06

gsaravanan
Messages: 16
Registered: April 2015
Location: chennai

Junior Member

Hi thanks you,There is N number of set of files in my input text file.
1)My input file always start with 20 and end 62F
2)each set have N number of :61 and 86 (if there is two 61,86 then i have to dispaly 2 rows,if its 3 then i have to dispaly 3 rows)

say for example below text is my input file.here frst set having :61,86 as two times so i want to read as this 2 rows,in second set file have 3 times same i have to read as 3 rows.

Hopw you got the clarity,please help me

:20:STARTUMSE
:25:10080000/1008546EXT
:28C:00002/001
:60F:C150409EUR51,12
:61:1312031203CR25,56N087NONREF
:86:?008345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:61:1401030103CR25,56N087NONREF
:86:??2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:62F:C150409EUR0,00

:20:STARTUMSE
:25:10080000/1009010EXT
:28C:00002/001
:60F:C150409EUR40,00
:61:1312171217CR20,00N087NONREF
:86:?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:61:1401160116CR20,00N087NONREF
:86:?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:61:131217hhf1217CR20,00N087NONREF
:86:?2133824df378?2228-JANf-15?3010080000?31332904500?
:62F:C150409EUR0,00

:20:STARTUMSE
:25:10080000/1009347EXT
:28C:00002/001
:60F:C150409EUR30,00
:61:1312031203CR15,00N087NONREF
:86:?0082345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:61:1401030103CR15,00N087NONREF
:86:?00845?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:62F:C150409EUR0,00

Report message to a moderator

Re: UTL File reader [message #636305 is a reply to message #636297]

Tue, 21 April 2015 02:15

gsaravanan
Messages: 16
Registered: April 2015
Location: chennai

Junior Member

I hope you people understand the logic, please help me on this asap.please revert me back if you need any clarity.Thank you

Report message to a moderator

Re: UTL File reader [message #636306 is a reply to message #636302]

Tue, 21 April 2015 02:22

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

CAn you not buid in BB's solution? Add an outer loop to start gnerating a new pair of rows when you encounter the string :20.

Report message to a moderator

Re: UTL File reader [message #636332 is a reply to message #636303]

Tue, 21 April 2015 14:39

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl> HOST TYPE c:\my_oracle_files\input.dat
:20:STARTUMSE
:25:10080000/1008546EXT
:28C:00002/001
:60F:C150409EUR51,12
:61:1312031203CR25,56N087NONREF
:86:?008345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww

:61:1401030103CR25,56N087NONREF
:86:??2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:62F:C150409EUR0,00

:20:STARTUMSE
:25:10080000/1009010EXT
:28C:00002/001
:60F:C150409EUR40,00
:61:1312171217CR20,00N087NONREF
:86:?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:61:1401160116CR20,00N087NONREF
:86:?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:61:131217hhf1217CR20,00N087NONREF
:86:?2133824df378?2228-JANf-15?3010080000?31332904500?
:62F:C150409EUR0,00

:20:STARTUMSE
:25:10080000/1009347EXT
:28C:00002/001
:60F:C150409EUR30,00
:61:1312031203CR15,00N087NONREF
:86:?0082345?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffww
w
:61:1401030103CR15,00N087NONREF
:86:?00845?200?2133824378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww
:62F:C150409EUR0,00

SCOTT@orcl> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl> DECLARE
  2    v_in_file          UTL_FILE.FILE_TYPE;
  3    v_out_file         UTL_FILE.FILE_TYPE;
  4    v_in_line          VARCHAR2(32767);
  5    v_out_start        VARCHAR2(32767);
  6    TYPE v_tab IS TABLE OF VARCHAR2(32767);
  7    v_tab61         v_tab := v_tab();
  8    v_tab86         v_tab := v_tab();
  9    v_out_end          VARCHAR2(32767);
 10    v_out_line         VARCHAR2(32767);
 11  BEGIN
 12    -- open files:
 13    v_in_file := UTL_FILE.FOPEN ('MY_DIR', 'input.dat', 'R');
 14    v_out_file := UTL_FILE.FOPEN ('MY_DIR', 'output.dat', 'W');
 15    -- read and write data:
 16    LOOP
 17      BEGIN
 18        UTL_FILE.GET_LINE (v_in_file, v_in_line, 32767);
 19        IF SUBSTR (v_in_line, 2, 2) = '61' THEN
 20          v_tab61.EXTEND;
 21          v_tab61(v_tab61.LAST) := v_in_line;
 22        ELSIF SUBSTR (v_in_line, 2, 2) = '86' THEN
 23          v_tab86.EXTEND;
 24          v_tab86(v_tab86.LAST) := v_in_line;
 25        ELSIF SUBSTR (v_in_line, 2, 3) = '62F' THEN
 26          v_out_end := v_out_end || v_in_line;
 27          FOR i IN 1 .. v_tab61.COUNT LOOP
 28            v_out_line := v_out_start || v_tab61(i) || v_tab86(i) || v_out_end;
 29            UTL_FILE.PUT_LINE (v_out_file, v_out_line);
 30          END LOOP;
 31          v_tab61 := v_tab();
 32          v_tab86 := v_tab();
 33          v_out_start := NULL;
 34          v_out_end := NULL;
 35        ELSE
 36          v_out_start := v_out_start || v_in_line;
 37        END IF;
 38      EXCEPTION
 39        WHEN NO_DATA_FOUND THEN
 40          EXIT;
 41      END;
 42    END LOOP;
 43    -- close files:
 44    UTL_FILE.FCLOSE (v_in_file);
 45    UTL_FILE.FCLOSE (v_out_file);
 46  END;
 47  /

PL/SQL procedure successfully completed.

SCOTT@orcl> HOST TYPE output.dat
:20:STARTUMSE:25:10080000/1008546EXT:28C:00002/001:60F:C150409EUR51,12:61:1312031203CR25,56N087NONREF:86:?008345?200?21338243
78?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww:62F:C150409EUR0,00
:20:STARTUMSE:25:10080000/1008546EXT:28C:00002/001:60F:C150409EUR51,12:61:1401030103CR25,56N087NONREF:86:??2133824378?2228-JA
N-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww:62F:C150409EUR0,00
:20:STARTUMSE:25:10080000/1009010EXT:28C:00002/001:60F:C150409EUR40,00:61:1312171217CR20,00N087NONREF:86:?2133824378?2228-JAN
-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww:62F:C150409EUR0,00
:20:STARTUMSE:25:10080000/1009010EXT:28C:00002/001:60F:C150409EUR40,00:61:1401160116CR20,00N087NONREF:86:?200?2133824378?2228
-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww:62F:C150409EUR0,00
:20:STARTUMSE:25:10080000/1009010EXT:28C:00002/001:60F:C150409EUR40,00:61:131217hhf1217CR20,00N087NONREF:86:?2133824df378?222
8-JANf-15?3010080000?31332904500?:62F:C150409EUR0,00
:20:STARTUMSE:25:10080000/1009347EXT:28C:00002/001:60F:C150409EUR30,00:61:1312031203CR15,00N087NONREF:86:?0082345?200?2133824
378?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww:62F:C150409EUR0,00
:20:STARTUMSE:25:10080000/1009347EXT:28C:00002/001:60F:C150409EUR30,00:61:1401030103CR15,00N087NONREF:86:?00845?200?213382437
8?2228-JAN-15?3010080000?31332904500?32dasfwgfwrgwrgghehhjjjyj?35Commerzbank Fil I Berlin ?36ffffwww:62F:C150409EUR0,00

SCOTT@orcl>

The output may not look right, due to wrapping around when displayed, but in the actual file there are seven lines.

[Updated on: Tue, 21 April 2015 14:42]

Report message to a moderator

Previous Topic:	Hierarchical queries with aggregation
Next Topic:	SQL query for count

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 20:57:23 CDT 2024