oracle 外部表加载txt文件-先转为csv文件再导入-ok

已于 2022-07-02 16:35:24 修改
阅读量607 收藏
点赞数
分类专栏： oracle 文章标签： oracle
于 2022-07-02 16:33:12 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013737399/article/details/125574565
版权

1、将windows上文件的格式从UTF-16转为utf-8

[root@Cloud-Server ~]# file 111111.txt
111111.txt: Little-endian UTF-16 Unicode text, with CRLF line terminators
[root@Cloud-Server ~]# 
[root@Cloud-Server ~]# iconv -f utf-16 -t utf-8 111111.txt > 222222.txt
[root@Cloud-Server ~]# 
[root@Cloud-Server ~]# file 222222.txt
222222.txt: UTF-8 Unicode text, with CRLF line terminators
[root@Cloud-Server ~]# 

原始txt 文件格式如下

[root@Cloud-Server ~]# more /root/222222.txt |head -10
102100014904:中国工商银行股份有限公司北京岳各庄支行
102100002003:中国工商银行股份有限公司北京白云路支行
102100000064:中国工商银行股份有限公司北京樱桃园支行
102100000185:中国工商银行股份有限公司北京菜市口支行
102100009795:中国工商银行股份有限公司北京中航油支行
102100001153:中国工商银行股份有限公司北京城关支行
102100001274:中国工商银行股份有限公司北京燕莎支行
102100002484:中国工商银行股份有限公司北京天宁寺支行
102100022099:中国工商银行股份有限公司北京酒仙桥支行
102100023737:中国工商银行股份有限公司北京科技园东区支行
[root@Cloud-Server ~]# 

2、使用python3 将txt转为csv文件

import csv

out = open('222222.csv','w',newline='')
csv_writer = csv.writer(out,dialect='excel')  

f = open("/root/222222.txt","r")
for line in f.readlines():
    line=line.replace(':','\t')
    list = line.split()
    csv_writer.writerow(list)

# 检查csv文件
# 读取csv文件的前10行
import csv
with open('/root/222222.csv') as csvfile:
    reader = csv.reader(csvfile)
    for i,row in enumerate(reader):
        #print(row['BANKCODE'], row['BANKNAME'])
        print(row)
        if(i >= 9):
            break
    
# 输出结果如下：
['102100014904', '中国工商银行股份有限公司北京岳各庄支行']
['102100002003', '中国工商银行股份有限公司北京白云路支行']
['102100000064', '中国工商银行股份有限公司北京樱桃园支行']
['102100000185', '中国工商银行股份有限公司北京菜市口支行']
['102100009795', '中国工商银行股份有限公司北京中航油支行']
['102100001153', '中国工商银行股份有限公司北京城关支行']
['102100001274', '中国工商银行股份有限公司北京燕莎支行']
['102100002484', '中国工商银行股份有限公司北京天宁寺支行']
['102100022099', '中国工商银行股份有限公司北京酒仙桥支行']
['102100023737', '中国工商银行股份有限公司北京科技园东区支行']

查看最终生成的csv文件的前10行数据如下：

[root@Cloud-Server ~]# more /home/oracle/222222.csv |head -10
102100014904,中国工商银行股份有限公司北京岳各庄支行
102100002003,中国工商银行股份有限公司北京白云路支行
102100000064,中国工商银行股份有限公司北京樱桃园支行
102100000185,中国工商银行股份有限公司北京菜市口支行
102100009795,中国工商银行股份有限公司北京中航油支行
102100001153,中国工商银行股份有限公司北京城关支行
102100001274,中国工商银行股份有限公司北京燕莎支行
102100002484,中国工商银行股份有限公司北京天宁寺支行
102100022099,中国工商银行股份有限公司北京酒仙桥支行
102100023737,中国工商银行股份有限公司北京科技园东区支行
[root@Cloud-Server ~]# 

3、需要将/root/222222.csv 传到数据库服务器上

cp /root/222222.csv /home/oracle/222222.csv
chown oracle:oinstall /home/oracle/222222.csv

4、将远程连接工具的字符集设置为G2312  （好像可以省略,在后期查看数据的时候可能会需要进行设置）

5、设置操作系统的字符集与数据库字符集保持一致（好像可以省略）

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

6、创建表

drop table LG_BANKNO_BNB_20220620 purge ;
create table LG_BANKNO_BNB_20220620(BANKCODE number,BANKNAME varchar2(400)) ;

7、创建sqlldr的控制文件

$ mkdir -p /home/oracle/external_tb/data
create or replace directory data_dir as '/home/oracle/';
grant read,write on directory data_dir to public;

8、创建外部表

drop table emp_new purge ;
create table emp_new(
                    BANKCODE number,
                    BANKNAME varchar2(400)
                    )
            organization external
                    (
                    type oracle_loader
                    default directory data_dir
                    access parameters(
                                    records delimited by newline
                                    badfile 'emp_new%a_%p.bad'
                                    logfile 'emp_new%a_%p.log'
                                    fields terminated by ','
                                    optionally enclosed by '"'
                                    lrtrim missing field values are null
                                    reject rows with all null fields
                                    )
                    location ('222222.csv')
)
parallel 
reject limit unlimited;

说明:即使存在不满足要求的数据，也会在创建外部表的时候能正常创建成功。

9、检查数据

select count(*) from emp_new ;

  COUNT(*)
----------
    126234

set lines 200 pages 200
col BANKCODE for 9999999999999999999
col BANKNAME for a70
select * from emp_new where rownum <=10 ;

            BANKCODE BANKNAME
-------------------- ----------------------------------------------------------------------
        102100014904 中国工商银行股份有限公司北京岳各庄支行
        102100002003 中国工商银行股份有限公司北京白云路支行
        102100000064 中国工商银行股份有限公司北京樱桃园支行
        102100000185 中国工商银行股份有限公司北京菜市口支行
        102100009795 中国工商银行股份有限公司北京中航油支行
        102100001153 中国工商银行股份有限公司北京城关支行
        102100001274 中国工商银行股份有限公司北京燕莎支行
        102100002484 中国工商银行股份有限公司北京天宁寺支行
        102100022099 中国工商银行股份有限公司北京酒仙桥支行
        102100023737 中国工商银行股份有限公司北京科技园东区支行

10 rows selected.

10、检查日志文件，在/home/oracle/目录下

[oracle@Cloud-Server ~]$ 
[oracle@Cloud-Server ~]$ ls -lrt |grep emp_new
-rw-r--r-- 1 oracle oinstall  8668743 Jun 20 21:06 222222.txt
-rw-r--r-- 1 oracle oinstall     1356 Jul  2 14:10 emp_new000_22513.log
-rw-r--r-- 1 oracle oinstall     3996 Jul  2 14:10 emp_new000_20990.log
-rw-r--r-- 1 oracle oinstall     1728 Jul  2 14:10 emp_new000_20990.bad

查看未导入的数据，一定要在加载完外部文件中所有数据后再检查，否则可能由于没有读取到存在问题的数据而未生产bad文件，导致结果异常。

[oracle@Cloud-Server ~]$ more emp_new000_20990.bad
java.net.ConnectException: Connection timed out: connect
        at java.net.DualStackPlainSocketImpl.connect0(Native Method)
        at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
        at java.net.Socket.connect(Socket.java:589)
        at java.net.Socket.connect(Socket.java:538)
        at sun.net.NetworkClient.doConnect(NetworkClient.java:180)
        at sun.net.www.http.HttpClient.openServer(HttpClient.java:463)
        at sun.net.www.http.HttpClient.openServer(HttpClient.java:558)
        at sun.net.www.http.HttpClient.<init>(HttpClient.java:242)
        at sun.net.www.http.HttpClient.New(HttpClient.java:339)
        at sun.net.www.http.HttpClient.New(HttpClient.java:357)
        at sun.net.www.protocol.http.HttpURLConnection.getNewHttpClient(HttpURLConnection.java:1220)
        at sun.net.www.protocol.http.HttpURLConnection.plainConnect0(HttpURLConnection.java:1156)
        at sun.net.www.protocol.http.HttpURLConnection.plainConnect(HttpURLConnection.java:1050)
        at sun.net.www.protocol.http.HttpURLConnection.connect(HttpURLConnection.java:984)
        at com.system.common.util.SHbank.SHNSBankUtil.hiscomm(SHNSBankUtil.java:149)
        at com.system.common.util.SHbank.SHNSBankUtil.getBankList(SHNSBankUtil.java:573)
        at com.system.common.util.SHbank.SHNSBankUtil.download(SHNSBankUtil.java:592)
        at com.system.common.util.SHbank.SHNSBankUtil.main(SHNSBankUtil.java:966)
[oracle@Cloud-Server ~]$ 
[oracle@Cloud-Server ~]$ more emp_new000_22513.log

 LOG file opened at 07/02/22 14:09:59

KUP-05008:   Warning: Intra source concurrency disabled because the input data is in DELIMITED record format and the characterset is neither fixed width nor UTF8.

Field Definitions for table EMP_NEW
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

    BANKCODE                        CHAR (255)
      Terminated by ":"
      Enclosed by """ and """
      Trim whitespace from left and right
    BANKNAME                        CHAR (400)
      Terminated by ":"
      Enclosed by """ and """
      Trim whitespace from left and right


 LOG file opened at 07/02/22 14:10:38

KUP-05008:   Warning: Intra source concurrency disabled because the input data is in DELIMITED record format and the characterset is neither fixed width nor UTF8.

Field Definitions for table EMP_NEW
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

    BANKCODE                        CHAR (255)
      Terminated by ":"
      Enclosed by """ and """
      Trim whitespace from left and right
    BANKNAME                        CHAR (400)
      Terminated by ":"
      Enclosed by """ and """
      Trim whitespace from left and right
[oracle@Cloud-Server ~]$ 
[oracle@Cloud-Server ~]$ more emp_new000_20990.log


 LOG file opened at 07/02/22 14:09:59

KUP-05008:   Warning: Intra source concurrency disabled because the input data is in DELIMITED record format and the characterset is neither fixed width nor UTF8.

Field Definitions for table EMP_NEW
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

    BANKCODE                        CHAR (255)
      Terminated by ":"
      Enclosed by """ and """
      Trim whitespace from left and right
    BANKNAME                        CHAR (400)
      Terminated by ":"
      Enclosed by """ and """
      Trim whitespace from left and right


 LOG file opened at 07/02/22 14:10:38

KUP-05008:   Warning: Intra source concurrency disabled because the input data is in DELIMITED record format and the characterset is neither fixed width nor UTF8.

Field Definitions for table EMP_NEW
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

    BANKCODE                        CHAR (255)
      Terminated by ":"
      Enclosed by """ and """
      Trim whitespace from left and right
    BANKNAME                        CHAR (400)
      Terminated by ":"
      Enclosed by """ and """
      Trim whitespace from left and right
error processing column BANKCODE in row 125778 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125779 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125780 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125781 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125782 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125783 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125784 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125785 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125786 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125787 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125788 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125789 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125790 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125791 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125792 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125793 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125794 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125795 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125796 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125797 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125798 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125799 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125800 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
error processing column BANKCODE in row 125801 for datafile /home/oracle/222222.txt
ORA-01722: invalid number
[oracle@Cloud-Server ~]$ 

ksf泡面
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
导入txt文件到oracle数据库
03-26
把txt文件数据导入到oracle数据库当中，进行保存，使用map进行操作
导入oracle文件,txt文件导入oracle方法
weixin_39874350的博客
04-03 1777
在实际工作中，经常要从数据库中导出数据或往数据库中导入数据。当遇到数据量较大时，就需要导入文本格式的数据，一般导入的数据格式有txt，csv等。以下主要介绍我在实际工作中向oracle中导入数据的两个实例。1.使用PL/SQL D在实际工作中，经常要从数据库中导出数据或往数据库中导入数据。当遇到数据量较大时，就需要导入文本格式的数据，一般导入的数据格式有txt，csv等。以下主要介绍我在实际工作中...
oracle导入txt数据的脚本
06-13
oracle导入txt数据的脚本
oracle导入txt文件
Rambo
07-15 467
1建一个ctl文件 例如control.ctl 内容 Load data infile 'F:\demo\AAA_01_20130518_0005_9242.TXT'//导入文件的路径 into table Dat_src_c_interface //导入到数据库中的表名 append fields terminated by ' '//字段用什么分开的 ( act...
txt文件导入oracle数据库
09-18
txt文件导入oracle数据库
oracle数据库导入TXT文件方法介绍
08-29
主要介绍了oracle数据库导入TXT文件方法介绍，文中向大家展示了具体代码示例，需要的朋友可以参考下。
如何利用Oracle外部表导入文本文件的数据
12-15
　Oracle外部表支持两种类型的驱动：一种是ORACLE_LOADER，外部表的数据必须来源于文件文件，另一种则是ORACLE_DATAPUMP，外部表的数据必须是二进制dump文件，该dump文件是先前将Oracle内部表的数据导入到外部表中...
oracle创建表空间、授权、创建用户、导入dmp文件
09-09
主要介绍了oracle创建表空间、授权、创建用户、导入dmp文件,需要的朋友可以参考下
Oracle SQLPlus导出数据到csv文件的方法
09-08
总的来说，Oracle SQLPlus提供了灵活的方式来导出数据到CSV文件，用户可以根据具体需求调整输出设置，以达到最佳的导出效果。无论是简单的数据导出还是复杂的数据操作，SQLPlus都能提供高效且可控的解决方案。通过...
java-Web项目部署步骤之Oracle数据库导入.dmp文件数据
12-14
一、搭建环境 安装java开发人员需要的工具， ...Oracle通过dos命令行窗口导入dmp文件的步骤： –(1)创建表空间 create tablespace test04 datafile ‘D:\a_oracle_table\test04.dbf’ size 1024M a
如何通过txt文件批量导入数据到oracle数据库
11-02
可以通过对存储了大量记录的txt文件进行拆分处理形成数组，进而实现批量导入数据到数据库。
oracle 导入txt
Mchange的专栏
06-28 873
1899800 广东省东莞市 电信 0769 1899801 广东省东莞市 电信 0769 如上，有25w条手机号归属地的记录要入库，文件是excel格式的 首先，建表，4个字段； 然后，将excel另存为txt文件，选择制表符分隔； 在txt文件同一目录，新建文件load.
oracle 导txt,oracle 导入txt
weixin_39737764的博客
04-03 241
1899800 广东省东莞市 电信 07691899801 广东省东莞市 电信 0769如上，有25w条手机号归属地的记录要入库，文件是excel格式的首先，建表，4个字段；然后，将excel另存为txt文件，选择制表符分隔；在txt文件同一目录，新建文件load.ctl,字符集ZHS16GBK的文本文件的控制文件load.ctl...
Oracle 如何将txt文件中的数据导入数据库
最新发布
Keep learing, and stay fast
03-05 1452
使用文本导入器，可以将ASCII文件导入数据库
oracle 如何导入txt,Oracle中导入TXT并进行处理
weixin_31423211的博客
04-06 2251
欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 Oracle数据库工作中常会遇到将TXT文件导入到Oracle中进行处理的问题，这样可以对TXT中数据进行分析处理。下面就将完整的导入及处理方法列出，当做参照吧。 大多来自其他部门的数据都是以.xls形式存欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入Oracle数据库工作中常会遇到将...
oracle打开 txt文件,oracle 导入txt文件
weixin_39782573的博客
04-03 623
第一次接触Oracle，记录一下导入txt文件的过程。首先，导入txt文件需要格式上整齐，类似下图： 然后Oracle创建表：create table OUTCLINICAL2DIM(MPI_PERSON_ID varchar(20) not null primary key,BIT01 int,BIT02 int);假设你要导入你的数据为ICD.txt文档下面你要写个.ctl文档,名字叫data...
【oracle sqlldr 导入 txt csv大文件】
qq464917671的博客
03-19 829
【Oracle】【sqlldr】
oracle导入txt路径,oracle数据库导入TXT文件方法介绍
weixin_34300871的博客
04-02 293
客户端连接数据库导入1. 安装有oracle客户端，配好监听。2. 以oracle数据库app用户的表user_svc_info为例解释说明：infile 'info.txt' 表示要导入的文本文件名为info.txtappend into table 后接要导入的表名此处用append表示追加到表中，若用Insert 表示导入空表，有数据则停止；Replace表示原来表中如果有数据，则会被删除(...
oracle导入TXT文件
weixin_30414155的博客
09-04 187
oracle导入TXT文件： 1、建好对应的表和字段；2、新建test.ctl文件,用记事本编辑写入: OPTIONS (skip=1) load data INFILE 'E:\8080.txt' --修改对应的路径和文件名 append into table account_of_check --修改对应的表名 FIELDS TERMINATED BY ''　　　　...
sqlload 导入编码KOI8-R 的csv文件
07-15
要使用sqlldr命令将编码为KOI8-R的CSV文件导入到Oracle数据库，你可以按照以下步骤操作： 1. 确保你已经安装了Oracle客户端，并且已经设置了正确的环境变量（如ORACLE_HOME）。 2. 创建一个控制文件（control file），它描述了CSV文件的格式和字段映射关系。例如，你可以创建一个名为example.ctl的文件，内容如下： ``` LOAD DATA INFILE 'your_file.csv' BADFILE 'your_file.bad' DISCARDFILE 'your_file.dsc' APPEND INTO TABLE your_table CHARACTERSET KOI8R FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( column1, column2, ... ) ``` 在上面的示例中，你需要将'your_file.csv'替换为实际的CSV文件路径，'your_table'替换为目标表的名称，以及适当地指定列名和数据类型。 3. 打开命令行窗口或终端，并导航到Oracle客户端的安装目录。 4. 运行以下命令导入数据： ``` sqlldr username/password@database control=example.ctl log=example.log ``` 将'username'和'password'替换为你的数据库凭据，'database'替换为你要连接的数据库名。 5. 这将开始导入过程，并将日志输出到example.log文件中。你可以查看日志文件以获取导入的详细信息和任何错误。 请注意，上述步骤中的一些参数和设置可能需要根据你的具体情况进行调整。确保根据你的需求和环境进行适当的更改。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值