如何在sql-loader中使用解码? [英] How to use decode in sql-loader?

问题描述

我使用sqlldr导入CSV文件，并且日期多种格式存在一些问题.

I use sqlldr to import CSV files and I have some problem with date multiple formats.

CSV文件中的日期为 DD/MM/YYYY ，如果没有日期，则为单个点

Dates inside the CSV file are DD/MM/YYYY and if there is no date it is a single dot

DATE_COLUMN;OTHER_COLUMN
01/01/2013;other column content 1
.;other column content 2

---

我的sqlldr的.ctl文件

---

---

My .ctl file for sqlldr

---

LOAD DATA
INFILE '/path/to/my/file.csv'
REPLACE INTO TABLE table_to_fill
FIELDS TERMINATED BY ';'
(
COLUMNDATE "decode(:COLUMNDATE ,NULL,'.', to_date(:COLUMNDATE ,'DD/MM/YYYY'))",
OTHER_COLUMN
)

当我使用时，导入工作正常:

The import is working when I use :

decode(:COLUMNDATE ,NULL,'.'))

或

to_date(:COLUMNDATE ,'DD/MM/YYYY')

但是当我尝试将两者结合在一起时……

But not when I try to combine both...

这是错误日志:

Record 1: Rejected - Error on table table_to_fill, column COLUMNDATE.
ORA-01858: a non-numeric character was found where a numeric was expected

请问如何将它们结合起来?

How can I combine these, please ?

我认为解码"功能的最后一个参数是该列的默认值，对吗?

I thought that the last parameter of the "decode" function was for the default value of the column, am I wrong ?

推荐答案

SQL Loader的常规"语法在这里就足够了.试试这个:

SQL Loader's "regular" syntax should be enough here. Try this:

LOAD DATA
INFILE '/path/to/my/file.csv'
REPLACE INTO TABLE table_to_fill
FIELDS TERMINATED BY ';'
(
  COLUMNDATE DATE(7) "DD/MM/YYYY" NULLIF COLUMNDATE = "."
  OTHER_COLUMN
)

这篇关于如何在sql-loader中使用解码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！