user613114 user613114 - 1 month ago 7
SQL Question

Oracle PL-SQL : Import multiple delimited files into table

I have multiple files (f1.log, f2.log, f3.log etc)
Each file has the data in

;
&
=
delimited format. (new lines are delimited by
;
and fields are delimited by
=
) e.g.

data of f1:

1=a;2=b;3=c


data of f2:

1=p;2=q;3=r


I need to read all these files and import data into table in format:

filename number data

f1 1 a

f1 2 b

f1 3 c

f2 1 p
[...]


I am new to SQL. Can you please guide me, how can do it?

Answer

Use SQL*Loader to get the files into a table. Assuming you have a table created a bit like:

create table FLOG
(
  FILENAME   varchar2(1000)
 ,NUM        varchar2(1000)
 ,DATA       varchar2(1000)
);

Then you can use the following control file:

LOAD DATA
INFILE 'f1.log' "str ';'"
truncate INTO TABLE flog
fields terminated by '=' TRAILING NULLCOLS
(
   filename constant 'f1'
   ,num  char 
   ,data char 
)

However, you will need a different control file for each file. This can be done by making the control file dynamically using a shell script. A sample shell script can be:

cat >flog.ctl <<_EOF
LOAD DATA
INFILE '$1.log' "str ';'"
APPEND INTO TABLE flog
fields terminated by '=' TRAILING NULLCOLS
(
filename constant '$1'
,num  char
,data char
)
_EOF

sqlldr <username>/<password>@<instance> control=flog.ctl data=$1.log

Saved as flog.sh it can then be run like:

./flog.sh f1
./flog.sh f2
Comments