Oracle 如何将重复的类似结构的数据格式从纯文本文件加载到DB表？

嗨，Oracle大师

我被分配了从文本文件 (例如myreport.txt) 加载数据到Oracle表的要求。
文本文件包含学生每个学科的标记数据集。
文件myreport.txt
--------
第十个标准结果
在2017年8月05日上宣布

学生-Ajay Kumar Roll No-123456学院-XYZ学校
Seq子标记
1 sup1 90
2 Sub2 87
3 Sub3 NA
4亚4钠

结果: 重考

学生-Vijay Kumar Roll No-123457学院-ABC学校
Seq子标记
1 sup1 90out of 100
2 Sub2 87out of 100
3 Sub3 70
4 Sub5 89

结果: 通过

学生-Manoj Kumar Roll No-123458学院-PQR学校
Seq子标记
1子40
2苏比50
3 SubZ 10

结果: 失败
-

假设我有一个带有列的表result_of_tenth_std
年份，学校，student_name，student_rollno，subject_seq，subject_name，标记，result_status (此col包含学生所有行的相同值)

问: 将这些数据从文件加载到数据库表的方法是什么？

我会让你开始这个概念

a) 外部表以SQL的方式访问报表

SQL> CREATE TABLE rep
  2   (
  3   LINE varchar2(1000),
  4   student varchar2(180),
  5   result varchar2(20)
  6   )
  7   ORGANIZATION external
  8   (
  9   TYPE oracle_loader
 10   DEFAULT DIRECTORY temp
 11   ACCESS PARAMETERS
 12   (
 13   RECORDS DELIMITED BY NEWLINE
 14   READSIZE 1048576
 15   FIELDS LDRTRIM
 16   MISSING FIELD VALUES ARE NULL
 17   REJECT ROWS WITH ALL NULL FIELDS
 18   (
 19   line (1:1000) char(1000),
 20   student (9:200) char(180),
 21   result (9:200) char(20)
 22   )
 23   )
 24   location
 25   (
 26   'report.txt'
 27   )
 28   )REJECT LIMIT UNLIMITED
 29   /

Table created.

SQL> select line from rep;

LINE
---------------------------------------------------------------
10th standard results
declared on 05 aug 2017
Student-Ajay Kumar Roll No-123456 College-XYZ School
Seq Sub Marks
1 Sub1 90
2 Sub2 87
3 Sub3 NA
4 Sub4 NA
Result: retake exam
Student-Vijay Kumar Roll No-123457 College-ABC School
Seq Sub Marks
1 Sub1 90 out of 100
2 Sub2 87 out of 100
3 Sub3 70
4 Sub5 89
Result: Passed
Student-Manoj Kumar Roll No-123458 College-PQR School
Seq Sub Marks
1 SubX 40
2 SubY 50
3 SubZ 10
Result: Failed
复制

请注意，字段可以有重叠-对于固定宽度定义，这很好

b) 使用文件行和last_value对学生行进行分组

SQL> with t as (
  2     select rownum r, r.* from rep r
  3  )
  4  select
  5    last_value(case when line like 'Student%' then r end ignore nulls) over ( order by r ) student_row,
  6    substr(student,1,instr(student,'Roll No')-1) student_name,
  7    case when line like 'Result%' then result end result
  8  from t
  9  /

STUDENT_ROW STUDENT_NAME                                                 RESULT
----------- ------------------------------------------------------------ --------------------


          3 Ajay Kumar
          3
          3
          3
          3
          3
          3                                                              retake exam
         10 Vijay Kumar
         10
         10
         10
         10
         10
         10                                                              Passed
         17 Manoj Kumar
         17
         17
         17
         17
         17                                                              Failed

22 rows selected.


c) Grouping to bring the rows together


SQL> with t as (
  2     select rownum r, r.* from rep r
  3  ),
  4  extracted_fields as (
  5  select
  6    last_value(case when line like 'Student%' then r end ignore nulls) over ( order by r ) student_row,
  7    substr(student,1,instr(student,'Roll No')-1) student_name,
  8    case when line like 'Result%' then result end result
  9  from t
 10  )
 11  select student_row, max(student_name) student_name, max(result) result
 12  from extracted_fields
 13  group by student_row
 14  order by 1;

STUDENT_ROW STUDENT_NAME                                                 RESULT
----------- ------------------------------------------------------------ --------------------
          3 Ajay Kumar                                                   retake exam
         10 Vijay Kumar                                                  Passed
         17 Manoj Kumar                                                  Failed
复制



所以你可以扩展这个来挑选你喜欢的任何领域