Home » Developer & Programmer » Forms » Processing a text file (6i)
Processing a text file [message #290984] Wed, 02 January 2008 08:27 Go to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Dear all,
I am trying to read a text file in order to process it, and inserting it in a table but an error occurred each time the following is the code I work on it and the attached file contains a sample of the text file that I want to process, all these to process a time attendance data, the major problem that the data inserted but with null values:
--IF :BLOCK3.PATH IS NOT NULL THEN
DECLARE
count1 number(3):=0;
 P_FILE_NAME    VARCHAR2(100);
 RECORDS_file   Text_IO.File_Type;
 LINE           VARCHAR2(1000);

EMP_NO1              NUMBER(20);
TRANS_DATE1              NUMBER(10);
TRANS_TIME1               NUMBER(10);
TRANS_TYPE1             VARCHAR2(25);
TRANS_STATUS1           VARCHAR2(100);
linebuf 								varchar2(1000);

BEGIN

 P_FILE_NAME  := :BLOCK3.PATH;
 
 PUT_ZERO_LINE;
 
 RECORDS_file := Text_IO.Fopen(P_FILE_NAME, 'r');
 
 Text_IO.Get_Line(RECORDS_file, linebuf);
 MESSAGE(LINEBUF);MESSAGE(LINEBUF);

LOOP

 Text_IO.Get_Line(RECORDS_file, linebuf);
 --message(P_FILE_NAME||'1');
 --message(P_FILE_NAME||'1');
 

------------------------------------------------------------------
EMP_NO1              := TO_NUMBER  (GET_SUB_STR(LINE,1));
TRANS_DATE1          := (GET_SUB_STR(LINE,2));
TRANS_TIME1          := (GET_SUB_STR(LINE,3));
TRANS_TYPE1          := (GET_SUB_STR(LINE,4));
TRANS_STATUS1        := (GET_SUB_STR(LINE,5));

------------------------------------------------------------------
begin
--///////////////////////////////////////////
 INSERT INTO AAA (EMP_NO,TRANS_DATE,TRANS_TIME,TRANS_TYPE,TRANS_STATUS)
VALUES(EMP_NO1,TRANS_DATE1,TRANS_TIME1,TRANS_TYPE1,TRANS_STATUS1);
--///////////////////////////////////////////
message(TRANS_DATE1);message(TRANS_DATE1);
 
  Text_IO.New_Line;
    
  --EXIT WHEN EMP_NO1 = 000 ;
  
 END LOOP;

   COMMIT;
     MESSAGE('ok ');
   CLEAR_MESSAGE;
   CLEAR_MESSAGE;
 

   MESSAGE('PROCESSING CLOCK DATA COMPLETED');
  
 Text_IO.Fclose(RECORDS_FILE); 

 --EXCEPTION WHEN OTHERS THEN

     --Text_IO.Fclose(RECORDS_FILE); 


END LOOP;
--ELSE
	
	--MESSAGE('YOU SHOULD HAVE TO CHOSE A FILE');
	--MESSAGE(' ');
	
--END IF ;
END;

I hope you to help me ASAP.

Regards

[EDITED by LF: added [code] tags. Renamed the topic; the original title was "Mr"]


Text File Sample one of it's lines:
02/12/07 10:18:10,IDENT,FAILED,0,bioa,,0,0,5.4,65535,0,0,0,0,0

[Upd djmartin: Extra lines received via PM.]
  • Attachment: HQ.LOG
    (Size: 2.65KB, Downloaded 161 times)

[Updated on: Tue, 08 January 2008 17:15] by Moderator

Report message to a moderator

Re: Processing a text file [message #291839 is a reply to message #290984] Mon, 07 January 2008 00:56 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

Have you searched this forum for 'text_io'?

David
Re: Processing a text file [message #292252 is a reply to message #291839] Tue, 08 January 2008 05:12 Go to previous messageGo to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

No I do not solve it, I am still having the same problem.
Re: Processing a text file [message #292423 is a reply to message #292252] Tue, 08 January 2008 22:36 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
How many 'loop' commands do you have?

How many 'end loop' commands do you have?

Do these values match? If not, then you have a problem!

David
Re: Processing a text file [message #292434 is a reply to message #292423] Tue, 08 January 2008 23:07 Go to previous messageGo to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Hi ,
I serch for loops and ends of it I have 2 loops with 2 ends, but the problem now is the following erorr: ORA-302000, I search on the forum for this erorr but I can not find a specific reason there are many cuases for this erorr, the following is my code:
IF :BLOCK3.PATH IS NOT NULL THEN
DECLARE
count1 number(3):=0;
P_FILE_NAME VARCHAR2(100);
RECORDS_file Text_IO.File_Type;
LINE VARCHAR2(1000);

EMP_NO1 NUMBER(20);
TRANS_DATE1 NUMBER(10);
TRANS_TIME1 NUMBER(10);
TRANS_TYPE1 VARCHAR2(25);
TRANS_STATUS1 VARCHAR2(100);
linebuf varchar2(1000);

BEGIN

P_FILE_NAME := :BLOCK3.PATH;

PUT_ZERO_LINE;

RECORDS_file := Text_IO.Fopen(P_FILE_NAME, 'r');

Text_IO.Get_Line(RECORDS_file, linebuf);
MESSAGE(LINEBUF);MESSAGE(LINEBUF);

LOOP

Text_IO.Get_Line(RECORDS_file, linebuf);
--message(P_FILE_NAME||'1');
--message(P_FILE_NAME||'1');


------------------------------------------------------------------
EMP_NO1 := TO_NUMBER (GET_SUB_STR(LINE,1));
TRANS_DATE1 := (GET_SUB_STR(LINE,2));

TRANS_TIME1 := (GET_SUB_STR(LINE,3));
TRANS_TYPE1 := (GET_SUB_STR(LINE,4));
TRANS_STATUS1 := (GET_SUB_STR(LINE,5));

------------------------------------------------------------------
begin
--///////////////////////////////////////////
MESSAGE('10');
INSERT INTO AAA (EMP_NO,TRANS_DATE,TRANS_TIME,TRANS_TYPE,TRANS_STATUS)
VALUES(EMP_NO1,TRANS_DATE1,TRANS_TIME1,TRANS_TYPE1,TRANS_STATUS1);
--///////////////////////////////////////////
MESSAGE('1');

message(TRANS_DATE1);message(TRANS_DATE1);

Text_IO.New_Line;

EXIT WHEN EMP_NO1 = 000 ;

END LOOP;

COMMIT;
MESSAGE('ok ');
CLEAR_MESSAGE;
CLEAR_MESSAGE;


MESSAGE('PROCESSING CLOCK DATA COMPLETED');

Text_IO.Fclose(RECORDS_FILE);

--EXCEPTION WHEN OTHERS THEN

--Text_IO.Fclose(RECORDS_FILE);


END LOOP;
--ELSE

--MESSAGE('YOU SHOULD HAVE TO CHOSE A FILE');
--MESSAGE(' ');

END LOOP ;
END IF;
//////////////////////////////////////////////////////////
the attached file is a sample file for data to be processed.

What is your openion??


Regards,,,

  • Attachment: HQ.LOG
    (Size: 2.42KB, Downloaded 167 times)
Re: Processing a text file [message #292436 is a reply to message #292434] Tue, 08 January 2008 23:08 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
No, you have one 'loop' and two 'end loops'. Please format your code and place it between code tags when you post it.

David
Re: Processing a text file [message #292485 is a reply to message #292436] Wed, 09 January 2008 01:08 Go to previous messageGo to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Thank you for your response,
really now it is reading the text file but the problem now in inserting it in the table it inserting null values the following is last code:
DECLARE
count1 number(3):=0;
P_FILE_NAME VARCHAR2(100);
RECORDS_file Text_IO.File_Type;
LINE VARCHAR2(1000);

EMP_NO1 NUMBER(20);
TRANS_DATE1 NUMBER(10);
TRANS_TIME1 NUMBER(10);
TRANS_TYPE1 VARCHAR2(25);
TRANS_STATUS1 VARCHAR2(100);
linebuf varchar2(1000);

BEGIN

P_FILE_NAME := :BLOCK3.PATH;

PUT_ZERO_LINE;

RECORDS_file := Text_IO.Fopen(P_FILE_NAME, 'r');

Text_IO.Get_Line(RECORDS_file, linebuf);
MESSAGE(LINEBUF);MESSAGE(LINEBUF);

LOOP

Text_IO.Get_Line(RECORDS_file, linebuf);
--message(P_FILE_NAME||'1');
--message(P_FILE_NAME||'1');


------------------------------------------------------------------
EMP_NO1 := TO_NUMBER (SUBSTR(LINE,10,15));
TRANS_DATE1 := SUBSTR(LINE,1,5);

TRANS_TIME1 := SUBSTR(LINE,5,10);
TRANS_TYPE1 := SUBSTR(LINE,15,20);
TRANS_STATUS1 := SUBSTR(LINE,15,20);

------------------------------------------------------------------

--///////////////////////////////////////////
MESSAGE('10');
INSERT INTO AAA (EMP_NO,TRANS_DATE,TRANS_TIME,TRANS_TYPE,TRANS_STATUS)
VALUES(EMP_NO1,TRANS_DATE1,TRANS_TIME1,TRANS_TYPE1,TRANS_STATUS1);
--///////////////////////////////////////////
--MESSAGE('1');

message(TRANS_DATE1);message(TRANS_DATE1);

Text_IO.New_Line;

EXIT WHEN EMP_NO1 = 000 ;

END LOOP;

COMMIT;
MESSAGE('ok ');
CLEAR_MESSAGE;
CLEAR_MESSAGE;


MESSAGE('PROCESSING CLOCK DATA COMPLETED');

Text_IO.Fclose(RECORDS_FILE);

EXCEPTION WHEN OTHERS THEN

Text_IO.Fclose(RECORDS_FILE);


--END LOOP;
--ELSE

--MESSAGE('YOU SHOULD HAVE TO CHOSE A FILE');
--MESSAGE(' ');

--END LOOP ;


--END;
end;
///////////////////////////////////////////////////////


this is a sample of the line we need to process we need to take the date,time,status,empno from each line:
02/12/07 15:03:11,IDENT,OK,0,bioa,2750,0,0,0,11,0,0,0,0,0
So how we can do this??

Regards,,,



Re: Processing a text file [message #292774 is a reply to message #292485] Wed, 09 January 2008 18:31 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
With what part of "Please format your code and place it between code tags when you post it" are you having difficulties?

What are you trying to do with 'Text_IO.New_Line;'?

David
Re: Processing a text file [message #293384 is a reply to message #292774] Fri, 11 January 2008 23:17 Go to previous messageGo to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Dear,
For text_IO.New_Line, I am trying to process each line seperatly because each line contains employee data for time in and time out??

Regards,,,
Re: Processing a text file [message #293512 is a reply to message #293384] Sun, 13 January 2008 19:32 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please reread (or read) the utl_file documentation. New_line is associated with WRITING files. You do NOT use it when reading a file. Remove that line and see what happens.

David
icon14.gif  Re: Processing a text file [message #307301 is a reply to message #293512] Tue, 18 March 2008 06:53 Go to previous message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Really I would like to thank you for help in this topic, and yes you were right, thank you again.

Regards...
Previous Topic: Menu Module
Next Topic: How to call .ppt file --> 10g forms
Goto Forum:
  


Current Time: Sun Dec 11 08:30:53 CST 2016

Total time taken to generate the page: 0.04515 seconds