9 Replies Latest reply: Mar 31, 2013 5:52 AM by Andreas Weiden RSS

    text file reading

    Rdk
      Dear friends,

      I have some doubts regarding reading delimited data from text file.

      my agents sending text file with delimiters ( ~ ).

      Kindly suggest me which format advice to read the text file.

      I tested some datas with UTF-8 format text files

      Below is my code
      Declare
           
             in_file TEXT_IO.FILE_TYPE;
             linebuf VARCHAR2(2000);
             v_line varchar2(2000);
              first_var varchar2(10);
              
      Begin
           :control.filepath := GET_FILE_NAME('D:\TXT', File_Filter=>'All Files (*.*)|*.*|');
           in_file := TEXT_IO.FOPEN(:control.filepath,'r');
                          
           Loop
                  
                TEXT_IO.GET_LINE(in_file,linebuf);  
                   v_line := rtrim(ltrim(substr(linebuf,1,2000)));           

                first_var:=ltrim(Substr(v_line,1,instr(v_line,'~',1,1)-1));

                Message(first_var);
                Message(' ');     

                IF first_var='ABC' THEN

                         /*
                            || INSERT INTO TABLE A...
                         */
                       --
                ELSIF first_var ='XYZ' Then

                  /*
                            || INSERT INTO TABLE B...
                        */
                          
                END IF;
              End loop;

           Exception

           WHEN NO_DATA_FOUND THEN
                TEXT_IO.FCLOSE(in_file);
                Message('Inserted record sucessfully');
                Message(' ');
                Commit;
             
           When Others then
                TEXT_IO.FCLOSE(in_file);
                Message('others');
                Message(' ');
           

      End;
      My text files look this format

      ABC~123~456
      XYZ~123~456
      XYZ~123~456


      when it reading the first line it is giving some unknown character before Letter ABC

      I am facing error only in first line

      For example >?ABC

      How this unknown character is comming

      Regards

      Rvk