用Oracle sqlldr导入文本文件TXT

文件TBL_TRAD_ERM_TMP_A.ctl

内容：

LOAD DATA 

INTO TABLE TBL_TRAD_ERM_TMP 

APPEND

FIELDS TERMINATED BY ","  

(

CORE_SEQ_NO ,

BATCH_NO ,

FLOW_NO ,

CHANL_NO ,

FLAT_TRAD_DATE ,

FLAT_TRAD_TIME ,

FLAT_SYS_DATE ,

CHANL_FLOW_NO ,

BACK_FLOW_NO ,

CHANL_TRAD_NO ,

CUST_NO ,

CHANL_CUST_NO ,

CUST_NM ,

CUST_TYP ,

CUST_GRD ,

AMT1 ,

AMT3 ,

SVC ,

ACCT_NO ,

ACCT_TYPE ,

ACCT_NAME ,

ASS_ACCT_NO ,

ASS_ACCT_TYPE ,

ASS_ACCT_NAME ,

TRAD_BRAN ,

RETURN_CODE ,

TERM_INF ,

TRAD_STS ,

SIGN_BRAN ,

CERT_TYP ,

CERT_ID ,

TRAD_AREA_NO ,

TRAD_ABROAD_FLG ,

SIGN_AREA_NO ,

ID_AREA_NO ,

ACCT_SIGN_TYP ,

SIGN_TYPE ,

MERC_NO ,

NET_MBL ,

CCI_MBL ,

SIGN_DATE ,

OLD_CUST_GRD ,

LONG_IP ,

CUST_NO_LB ,

FLG1 ,

ACCT_BRAN ,

ASS_ACCT_BRAN ,

OPEN_AREA_NO ,

ASS_AREA_NO ,

CUST_AGE, 

NCIP_FLG,

EQUIPMENT_ID

)

文件：

transInfo0.txt

取两条数据

3000000,100,20121114143454360,03,20121114,143454,20121114,20121114143454360,20121114143454360,411101,312000088839,312000088839,test,A,1,21932.89,6835.13,1,1002000380989,01,test4,622000374776,01,test5,1000,20,1000,00,1000,1,312000088839,100,1,100,100,1,1,622000000043,622000000047,622000000035,20120102,1,100100100100,09,9,100,100,100,100,80,0,312000088839

3000001,100,20121114143454360,01,20121114,143454,20121114,20121114143454360,20121114143454360,411200,312000121945,312000121945,test5,A,1,80019.49,44252.71,1,1002000489652,01,test2,622000370554,01,test1,1000,20,1000,00,1000,1,312000121945,100,1,100,100,1,1,622000000042,622000000087,622000000069,20120102,1,100100100100,05,5,100,100,100,100,64,0,312000121945

表结构：

CREATE

    TABLE TBL_TRAD_ERM_TMP

    (

        CORE_SEQ_NO CHAR(20) NOT NULL,

        BATCH_NO NUMBER(5),

        FLOW_NO VARCHAR2(19) NOT NULL,

        CHANL_NO VARCHAR2(2),

        FLAT_TRAD_DATE VARCHAR2(8),

        FLAT_TRAD_TIME VARCHAR2(8),

        FLAT_SYS_DATE VARCHAR2(8),

        CHANL_FLOW_NO VARCHAR2(20),

        BACK_FLOW_NO VARCHAR2(20),

        CHANL_TRAD_NO VARCHAR2(7),

        CUST_NO VARCHAR2(20) NOT NULL,

        CHANL_CUST_NO VARCHAR2(30),

        CUST_NM VARCHAR2(80),

        CUST_TYP VARCHAR2(1),

        CUST_GRD VARCHAR2(1),

        AMT1 NUMBER(16,3),

        AMT3 NUMBER(15,2),

        SVC NUMBER(15,2),

        ACCT_NO VARCHAR2(30),

        ACCT_TYPE VARCHAR2(2),

        ACCT_NAME VARCHAR2(42),

        ASS_ACCT_NO VARCHAR2(30),

        ASS_ACCT_TYPE VARCHAR2(2),

        ASS_ACCT_NAME VARCHAR2(42),

        TRAD_BRAN VARCHAR2(9),

        RETURN_CODE VARCHAR2(20),

        TERM_INF VARCHAR2(20),

        TRAD_STS VARCHAR2(2),

        SIGN_BRAN VARCHAR2(9),

        CERT_TYP VARCHAR2(1),

        CERT_ID VARCHAR2(20),

        TRAD_AREA_NO VARCHAR2(9),

        TRAD_ABROAD_FLG VARCHAR2(1),

        SIGN_AREA_NO VARCHAR2(9),

        ID_AREA_NO VARCHAR2(9),

        ACCT_SIGN_TYP VARCHAR2(1),

        SIGN_TYPE VARCHAR2(1),

        MERC_NO VARCHAR2(15),

        NET_MBL VARCHAR2(22),

        CCI_MBL VARCHAR2(22),

        SIGN_DATE VARCHAR2(8),

        OLD_CUST_GRD VARCHAR2(1),

        LONG_IP NUMBER(12),

        CUST_NO_LB CHAR(2),

        FLG1 VARCHAR2(1),

        ACCT_BRAN VARCHAR2(9),

        ASS_ACCT_BRAN VARCHAR2(9),

        OPEN_AREA_NO VARCHAR2(6),

        ASS_AREA_NO VARCHAR2(6),

        CUST_AGE NUMBER(4),

        NCIP_FLG CHAR(1),

        EQUIPMENT_ID VARCHAR2(20),

        CONSTRAINT PK_TBL_TRAD_ERM_TMP PRIMARY KEY (CORE_SEQ_NO)

    )

命令：

sqlldr username/password control=TBL_TRAD_ERM_TMP.ctl data=transInfo0.txt 。

参考地址：http://blog.csdn.net/programpoet/article/details/4516377