Oracle PL-SQL:将多个定界文件导入表 [英] Oracle PL-SQL : Import multiple delimited files into table

问题描述

我有多个文件(f1.log，f2.log，f3.log等) 每个文件的数据均在;&中； =分隔格式. (用;分隔新行，用=分隔字段)

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.

f1的数据:

1=a;2=b;3=c

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
[...]

我是SQL新手.你能指导我，怎么做?

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

推荐答案

使用

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 
)

但是，每个文件将需要一个不同的控制文件.这可以通过使用Shell脚本动态制作控制文件来完成.一个示例外壳脚本可以是:

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

另存为flog.sh，然后可以像这样运行:

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

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

这篇关于Oracle PL-SQL:将多个定界文件导入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！