SQL Loader - Multiple Files and Grabbing file names

Question

I have a folder with over 400K txt files.

With names like

deID.RESUL_12433287659.txt_234323456.txt
deID.RESUL_34534563649.txt_345353567.txt
deID.RESUL_44235345636.txt_537967875.txt
deID.RESUL_35234663456.txt_423452545.txt

I want to store all the files and their content in the following way:

file_name                                      file_content
deID.RESUL_12433287659.txt_234323456.txt       Content 1
deID.RESUL_34534563649.txt_345353567.txt       Content 2
deID.RESUL_44235345636.txt_537967875.txt       Content 3
deID.RESUL_35234663456.txt_423452545.txt       Content 4

I tried creating Control file using:

LOAD
  DATA 
  INFILE 'deID.RESUL_12433287659.txt_234323456.txt'
  INFILE 'deID.RESUL_34534563649.txt_345353567.txt'
  INFILE 'deID.RESUL_44235345636.txt_537967875.txt'
  INFILE 'deID.RESUL_35234663456.txt_423452545.txt'
  APPEND INTO TABLE TBL_DATA
    EVALUATE CHECK_CONSTRAINTS
    REENABLE DISABLED_CONSTRAINTS
    EXCEPTIONS EXCEPTION_TABLE
  FIELDS TERMINATED BY "" 
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
  ( 
    FILE_NAME
  )

Is there a way I can grab the files names dynamically and specify wildcard in the INFILE so I don't have to mention 400K files one by one in my control file?

What is OS? Windows or Linux? – Dmitry Demin Feb 20 '19 at 11:07 — Dmitry Demin, Feb 20 '19 at 11:07
Can merge into one file? `cat *.txt >>all_test.txt` . – Dmitry Demin Feb 20 '19 at 11:20 — Dmitry Demin, Feb 20 '19 at 11:20
If I merge into one file, then I loose the file names? – dang Feb 20 '19 at 11:22 — dang, Feb 20 '19 at 11:22

score 2 · Accepted Answer · answered Feb 20 '19 at 15:29

1) Create table to hold data/files

create table TBL_DATA(file_name varchar2(4000), file_content clob);

2) Create load_all.ctl

LOAD DATA
INFILE file_list.txt
INSERT INTO TABLE TBL_DATA
APPEND
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    file_name          char(4000)
  , file_content                   LOBFILE(file_name) TERMINATED BY EOF
)

3) Redirect list of file to one file_list.txt ls -1 *.txt > file_list.txt

4) Run sqlldr user/pass@db control=load_all.ctl
5) load_all.ctl,file_list.txt and source files should be in the same folder.

SQL Loader - Multiple Files and Grabbing file names

1 Answers1

Linked