oracle 要导入的列中的 SQLLDR 和 NULL 字段检测
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6100946/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLLDR and NULL Field detection in column to be imported
提问by KriZ
SQLLDR and CTL file
SQLLDR 和 CTL 文件
Got a CSV file to import.
有一个要导入的 CSV 文件。
I want to specify in my CTL file to insert only into my table when i've got an empty value in a certain column in my CSV file. E.G I want to upload only user's records who don'thave a termination date.
我想在我的 CTL 文件中指定仅当我的 CSV 文件的某个列中有一个空值时才插入到我的表中。EG 我只想上传没有终止日期的用户记录。
I've tried various options
我尝试了各种选择
- When TERMINATIONDATE = ''
- When TERMINATIONDATE = ""
- When TERMINATIONDATE = null
- When TERMINATIONDATE = 'null'
- 当 TERMINATIONDATE = ''
- 当 TERMINATIONDATE = ""
- 当 TERMINATIONDATE = null
- 当 TERMINATIONDATE = 'null'
it only works when i'm using - When TERMINATIONDATE != '' then i get all my employees that have been terminated. But i want the opposite...
它仅在我使用时有效 - 当 TERMINATIONDATE != '' 然后我得到所有已被终止的员工。但我想要相反的...
Doesn't look like it's possible after all?
毕竟看起来不可能吗？
my CTL file
我的 CTL 文件
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'C:\temp\users.csv'
BADFILE 'C:\temp\users.bad'
DISCARDFILE 'C:\temp\users.dsc'
TRUNCATE
INTO TABLE "alawakaba"."users"
When TERMINATIONDATE = ''
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID,
FIRSTNAME,
LASTNAME,
FUNCTION,
DEPARTMENT,
COSTCENTER,
HIREDATE,
Field8 FILLER,
TERMINATIONDATE,
LOCCODE
)
(Column8 is just one i don't need so i'm skipping that)
（Column8 只是我不需要的，所以我跳过了）
tnxs for thy insights.
tnxs 为您的见解。
回答by Codo
It should work with the following clause:
它应该与以下条款一起使用：
WHEN TERMINATIONDATE = BLANKS
回答by KriZ
oh, got it.. It's not the ultimate solution and has some overhead but i can do it in 2 runs
哦，明白了。。这不是最终的解决方案，并且有一些开销，但我可以在 2 次运行中完成
a) run as CTL above.
Change the
When TERMINATIONDATE = ''
to
When TERMINATIONDATE != ''
a) 以上面的 CTL 运行。将
When TERMINATIONDATE = ''更改
为
When TERMINATIONDATE != ''
The discarded records will be injected into the DISCARDFILE 'C:\temp\users.dsc'now my discarded file will contain all the records i wanted to upload
丢弃的记录将被注入到DISCARDFILE 'C:\temp\users.dsc'现在我丢弃的文件将包含我想上传的所有记录
b) run a second CTL command to pickup the DSC file as input, don't skip records anymore and don't specify the when clause neither
b) 运行第二个 CTL 命令以获取 DSC 文件作为输入，不要再跳过记录，也不要指定 when 子句
LOAD DATA
INFILE 'C:\temp\users.dsc'
BADFILE 'C:\temp\users.bad'
DISCARDFILE 'C:\temp\users-run2.dsc'
TRUNCATE
INTO TABLE "alawakaba"."users"
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID,
FIRSTNAME,
LASTNAME,
FUNCTION,
DEPARTMENT,
COSTCENTER,
HIREDATE,
Field8 FILLER,
TERMINATIONDATE,
LOCCODE
)
Not the best or quickest one around but it does the trick .
不是最好的或最快的，但它确实有效。
can't believe that such a simple clause is not working from the start in SQLLDR. Anyway, hope it helps
无法相信这样一个简单的子句在 SQLLDR 中从一开始就不起作用。无论如何，希望它有帮助
K
钾
