Oracle UTL_FILE 读取 CSV 文件行 [英] Oracle UTL_FILE read CSV file lines

问题描述

我正在使用 UTL_FILE 包读取 csv 文件，然后在表中插入值，但我的问题是如何读取由逗号分隔的值..，这是我的代码:

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; 
        /

这是我的 csv 文件:

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 ?

问候.

推荐答案

这是一个示例，说明如何执行此操作.由于目录和文件名不同，我的代码与您的略有不同.

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.

代码；有趣的部分是第 14 行以及将整行拆分为单独值的方法:

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.

结果:

SQL> select * from test2;

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

SQL>

这篇关于Oracle UTL_FILE 读取 CSV 文件行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！