Sqlldr loader 编号列导入 [英] Sqlldr loader number column import

问题描述

下面是我的原始文件

br-Name-acc-Bal
10 B    LO  1,000.12-
10 C    SB  2,000.15
11 D    FD  5,000.00
12 E    RD  6,000.00
14 G    LO  8,000.56-
15 Q    LO  7,000.89-

我想使用 sqlldr 将这些数据导入到我的表中，并且我需要在 bal 列前使用 (-) 符号而不使用 (,) 逗号.

I want to import these data into my table using sqlldr and I need (-) symbol front of the bal column without (,) comma.

load data
infile *
truncate into table table1
fields 
trailing nullcols
(
Br    POSITION(1:2) ,
Name POSITION(4:5),
acc POSITION(6:7) ,
bal POSITION(10:18) ----->What should I mention here??? (Here am using datatype as number(17,3))
)

begindata

---

我正在寻找如下结果

---

I am looking for the result like below

你能帮我解决这个问题吗

Could you please help me on this

推荐答案

对于您发布的数据，它看起来像这样:取 BAL 值的子串，长度最多为 8 个字符，然后将其相乘按 -1 如果第 9 个字符是 -.

For data you posted, it would look like this: take substring of the BAL value up to 8 characters in length and multiply it by -1 if the 9th character is -.

目标表:

SQL> desc table1
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------
 BR                                                             VARCHAR2(10)
 NAME                                                           VARCHAR2(10)
 ACC                                                            VARCHAR2(10)
 BAL                                                            NUMBER

控制文件:

load data
infile *
truncate into table table1
fields 
trailing nullcols
(
Br    POSITION(1:2),
Name  POSITION(4:5),
acc   POSITION(6:7),
bal   POSITION(10:18) "to_number(substr(:bal, 1, 8), '9g999d99', 'nls_numeric_characters = .,') * 
                       case when substr(:bal, -1) = '-' then -1 else 1 end"
)

begindata
10 B LO  1,000.12-
10 C SB  2,000.15
11 D FD  5,000.00
12 E RD  6,000.00
14 G LO  8,000.56-
15 Q LO  7,000.89-

加载会话&结果:

Loading session & result:

SQL> $sqlldr scott/tiger@kc11gt control=test35.ctl log=test35.log

SQL*Loader: Release 11.2.0.1.0 - Production on Sri O×u 24 12:24:59 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5
Commit point reached - logical record count 6

SQL> select * from table1;

        BR NAME       ACC               BAL
---------- ---------- ---------- ----------
        10 B          LO           -1000,12
        10 C          SB            2000,15
        11 D          FD               5000
        12 E          RD               6000
        14 G          LO           -8000,56
        15 Q          LO           -7000,89

6 rows selected.

SQL>

没有显示 5000 和 6000 的小数，因为它是 NUMBER 数据类型列；如果您想查看小数，请使用带有适当格式掩码的 TO_CHAR 函数或(如果您使用的工具允许)设置数字格式，例如在 SQL*Plus 中

There are no decimals displayed for 5000 and 6000 because it is the NUMBER datatype column; if you want to see decimals, either use TO_CHAR function with appropriate format mask or (if tool you use allows it) set numeric format, such as in SQL*Plus

SQL> set numformat 9999d99
SQL> select * from table1;

BR         NAME       ACC             BAL
---------- ---------- ---------- --------
10         B          LO         -1000,12
10         C          SB          2000,15
11         D          FD          5000,00
12         E          RD          6000,00
14         G          LO         -8000,56
15         Q          LO         -7000,89

这篇关于Sqlldr loader 编号列导入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！