• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4069
  • Last Modified:

How to read a .CSV file using UTL_FILE

How to read a .CSV file using UTL_FILE
0
kingbinod
Asked:
kingbinod
3 Solutions
 
jazzIIIloveCommented:
Hi there;

I don't know if the following forum helps but let me give the link:
http://www.orafaq.com/forum/t/68404/0/

Best regards..
0
 
schwertnerCommented:
The best way is to use External Tables that are readonly, i.e. can be used only in SELECT statements
what ia more then enough.


create directory med as 'c:\med' ; 
GRANT READ  ON DIRECTORY med TO lsmedical;
GRANT WRITE ON DIRECTORY med TO lsmedical;
 
 
 
drop table lsmedical.USERMEDBASIC_EXT;
create table lsmedical.USERMEDBASIC_EXT ( 
                   PZN  VARCHAR2(33),
                   NAME VARCHAR2(111)   )
    organization external  
    (type oracle_loader  
     default directory med 
     access parameters (records delimited by newline 
     fields terminated by ',') 
     location ('med-de-formatted.txt','med-de-insulin-formatted.txt',
               'med-us-formatted.txt','med-us-insulin-formatted.txt')); 

Open in new window

0
 
Mark GeerlingsDatabase AdministratorCommented:
Here's a sample PL\SQL procedures that I wrote a number of years ago that just shows an example of using UTL_FILE for both reading and writing.  It is not specific to *.CSV files, but it may help:

create or replace procedure sample (dir_in in varchar2, file_nm in varchar2,
 lines_to_read in number default 3, chars_per_record in number default 999,
 dir_out in varchar2 default 'C:\temp') as
-- This procedure opens a text file in any directory, reads three lines from it
--  and writes them to C:\temp\temp.txt.
-- This is intended for large text files, to quickly get a small section of the file that can
--  be opened and browsed more easily in a text editor.
  in_file   utl_file.file_type;
  out_file  utl_file.file_type;
  text_str  varchar2(4000);
  lines     pls_integer;
  err_text  varchar2(200);
begin
  lines := 0;
  out_file := utl_file.fopen(dir_out,'temp.txt','W');
  in_file := utl_file.fopen(dir_in,file_nm,'R',chars_per_record);
  while lines < lines_to_read loop
    begin
      utl_file.get_line(in_file, text_str);
      utl_file.put_line(out_file,nvl(text_str,to_char(lines)));
      lines := lines +1;
    exception
      when others then
        err_text := substr(sqlerrm,1,200);
        lines := lines +1;
        utl_file.put_line(out_file,err_text);
    end;
  end loop;
  utl_file.fclose(in_file);
  utl_file.fclose(out_file);
end;
/
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now