Oracle UTL_FILE читать строки файла CSV
Я использую пакет UTL_FILE для чтения файла csv, затем вставляю значения в таблицу, но моя проблема заключается в том, как читать значения, разделенные запятыми.., это мой код:
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-файл:
100,Steven,King
101,Neena,Kochha
102,Lex,De Haan
103,Alexander
104,Bruce,Ernst
пожалуйста, у вас есть какие-либо предложения по моей проблеме?
С уважением .
Ответы 2
Вот пример, который показывает, как это сделать. Мой код немного отличается от вашего из-за разных имен каталогов и файлов.
Пример таблицы, которая будет содержать данные, хранящиеся в файле:
SQL> create table test2 (id number, fname varchar2(20), lname varchar2(20));
Table created.
Код; интересная часть - строка 14 и способ разбить всю строку на отдельные значения:
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>
Пожалуйста. На вашем месте я бы загрузил этот файл с помощью SQL Loader, используя параллельный вариант; не UTL_FILE (что означает, что вы избегаете медленных регулярных выражений). Даже если вы перепишете его в SUBSTR + INSTR, он все равно будет медленнее, чем SQL Loader.
@Littlefoot Как вы справляетесь с «Полями в кавычках»? Поля CSV, заключенные в двойные кавычки из-за того, что поле содержит 1 или более запятых?
В загрузчике SQL вы должны использовать НЕОБЯЗАТЕЛЬНО ЗАКЛЮЧЕННЫЙ параметр
В качестве альтернативы вы можете использовать "внешние таблицы" для чтения файла CVS. Таким образом, вы можете «запросить» файл как таблицу.
1-Если ваша база данных Oracle включена:
a-Windows, затем используйте «с разделителями '\r\n'»
b-Linux/unix, затем используйте "с разделителями '\n'"
2-В первой строке файла нет имен столбцов.
3-разделитель - запятая, ascii 44, ",".
4 поля могут иметь данные, заключенные в кавычки, ascii 34.
Таким образом, значения могут содержать пробелы, запятую и двойные кавычки как одну кавычку.
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;
большое спасибо, я впервые здесь о концепции «внешних таблиц», я постараюсь понять их. с уважением
У меня только один вопрос: «внешние таблицы» неявно используют загрузчик SQL?
большое спасибо, ваше решение отлично работает, у меня есть только один вопрос: применение «regexp_substr» к каждой строке может привести к снижению производительности, например, в случае, если у нас есть большой CSV-файл с более чем миллионом записей? С уважением !