Oracle UTL_FILE read CSV file lines

Question

i'm using UTL_FILE package to read a csv file , then insert values in table, but my issue is how to read values separated by Commas.. , this is my code :

     declare
         file1 UTL_FILE.FILE_TYPE;
         str varchar2(200 CHAR);
        begin
         file1 := UTL_FILE.FOPEN('DRCT1','test_file.csv','R');

         loop
          UTL_FILE.GET_LINE(file1,str);
-- here i want to read each value before the Commas then insert them in my table
-- insert statement..
          dbms_output.put_line(str);
         end loop;

        exception
        when no_data_found then
        UTL_FILE.FCLOSE(file1);

        end; 
        /

this is my csv file :

100,Steven,King
101,Neena,Kochha
102,Lex,De Haan
103,Alexander
104,Bruce,Ernst

please do you have any suggestion to my issue ?

Regards .

Littlefoot · Accepted Answer · 2019-05-27 16:37:56Z

3

Here's an example which shows how to do that. My code slightly differs from yours because of different directory and file names.

Sample table, which will contain data stored in the file:

SQL> create table test2 (id number, fname varchar2(20), lname varchar2(20));

Table created.

Code; interesting part is line 14 and the way to split the whole row into separate values:

SQL> declare
  2    l_file         utl_file.file_type;
  3    l_text         varchar2(32767);
  4    l_cnt          number;
  5  begin
  6    -- Open file.
  7    l_file := utl_file.fopen('EXT_DIR', 'test2.txt', 'R', 32767);
  8
  9    loop
 10      utl_file.get_line(l_file, l_text, 32767);
 11
 12      -- L_TEXT contains the whole row; split it (by commas) into 3 values
 13      -- and insert them into the TEST2 table
 14      insert into test2 (id, fname, lname)
 15        values (regexp_substr(l_text, '[^,]+', 1, 1),
 16                regexp_substr(l_text, '[^,]+', 1, 2),
 17                regexp_substr(l_text, '[^,]+', 1, 3)
 18               );
 19    end loop;
 20
 21    utl_file.fclose(l_file);
 22  exception
 23    when no_data_found then
 24      null;
 25  end;
 26  /

PL/SQL procedure successfully completed.

The result:

SQL> select * from test2;

        ID FNAME                LNAME
---------- -------------------- --------------------
       100 Steven               King
       101 Neena                Kochha
       102 Lex                  De Haan
       103 Alexander
       104 Bruce                Ernst

SQL>

answered May 27, 2019 at 16:37

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

James Over a year ago

thank you so much your solution works fine, i have just one question : applying "regexp_substr" to each line can cause performance degradation in case that we have a large csv file with more than million record for example ? Regards !

Littlefoot Over a year ago

You're welcome. If I were you, I'd load that file with SQL Loader, using parallel option; not UTL_FILE (which means that you'd avoid slow regular expressions). Even if you rewrite it to SUBSTR + INSTR, it'll still be way slower than SQL Loader

Code Novice Over a year ago

@Littlefoot How do you handle 'Quoted Fields'? CSV fields that are wrapped with double quotes due to the field containing 1 or more commas?

Littlefoot Over a year ago

In SQL Loader, you'd use the OPTIONALLY ENCLOSED parameter

Patrick Over a year ago

Tiny problem with this code sample is that you never explicitly close the file. In reality I guess it will automatically be closed when the handle goes out of scope at the end of the PL/SQL block

|

alvalongo · Accepted Answer · 2019-05-27 19:55:02Z

2

As an alternative you can use "external tables" to read the CVS file. So you can "query" the file as a table.

1-If your Oracle database is on:
a-Windows, then use "delimited by '\r\n'"
b-Linux/unix, then use "delimited by '\n'"

2-First row of file no have column names.

3-Delimiter is comma, ascii 44, ",".

4-Fields can have data enclosed between quotes, ascii 34.
So values can have spaces, comma, and double quotes as one quote.

  create table test_file_ext
  (id      number,
   fname   varchar2(200),
   lname   varchar2(200)
  )
  organization external
  (type oracle_loader
   default directory DRCT1
   access parameters (records      delimited by '\r\n'
                      badfile     'test_file_ext.bad'
                      discardfile 'test_file_ext.dis'
                      logfile     'test_file_ext.log'
                      fields terminated by ','
                             optionally enclosed by '"'
                             missing field values are null
                             reject rows with all null fields 
                     )
   location ('test_file.csv')
  )
  reject limit UNLIMITED;

edited May 27, 2019 at 19:55

answered May 27, 2019 at 19:06

alvalongo

6064 silver badges12 bronze badges

2 Comments

James Over a year ago

thank you so much , it(s the first time that i here about the concept of "external tables" , i will try to understand them. regards

James Over a year ago

I have just one question is "external tables" uses SQL Loader implicitly ?

Collectives™ on Stack Overflow

Oracle UTL_FILE read CSV file lines

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related