OPTIONS (skip=1,errors=30000)过滤掉第一行,设置允许错误条数,到了条数会自动停止
load data
infile 'c:\10.1-10.1010.csv'
append into table CH_JS_DZHGZXX_CS
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
xx_bs sequence(MAX,1) ,--类似于mysql的自增字段, csv文件中没有这一列
cllx INTEGER EXTERNAL NULLIF cllx=BLANKS,
wzhgzbh ,
fzrq DATE 'yyyy-mm-dd hh24:mi:ss' ,--格式化日期 注意csv文件格式要是ansi的 不要uinicode的
clzzqymc INTEGER EXTERNAL NULLIF clzzqymc=BLANKS,
clpp INTEGER EXTERNAL NULLIF clpp=BLANKS,
clmc INTEGER EXTERNAL NULLIF clmc=BLANKS,
clxh INTEGER EXTERNAL NULLIF clxh=BLANKS,
clsbdh INTEGER EXTERNAL NULLIF clsbdh=BLANKS,
csys INTEGER EXTERNAL NULLIF csys=BLANKS,
dpxh INTEGER EXTERNAL NULLIF dpxh=BLANKS,
dphgzbh INTEGER EXTERNAL NULLIF dphgzbh=BLANKS,
fdjxh INTEGER EXTERNAL NULLIF fdjxh=BLANKS,
fdjh INTEGER EXTERNAL NULLIF fdjh=BLANKS,
rlzl INTEGER EXTERNAL NULLIF rlzl=BLANKS,
pl INTEGER EXTERNAL NULLIF pl=BLANKS,
gl INTEGER EXTERNAL NULLIF gl=BLANKS,
yh INTEGER EXTERNAL NULLIF yh=BLANKS,
wkc INTEGER EXTERNAL NULLIF wkc=BLANKS,
wkk INTEGER EXTERNAL NULLIF wkk=BLANKS,
wkg INTEGER EXTERNAL NULLIF wkg=BLANKS,
ltgg INTEGER EXTERNAL NULLIF ltgg=BLANKS,
zs INTEGER EXTERNAL NULLIF zs=BLANKS,
edzzl INTEGER EXTERNAL NULLIF edzzl=BLANKS,
zqyzzl INTEGER EXTERNAL NULLIF zqyzzl=BLANKS,
edzk INTEGER EXTERNAL NULLIF edzk=BLANKS,
clzzrq DATE 'yyyy-mm-dd hh24:mi:ss' ,
clscdwmc INTEGER EXTERNAL NULLIF clscdwmc=BLANKS,
pzxlh INTEGER EXTERNAL NULLIF pzxlh=BLANKS,
lspzxlh INTEGER EXTERNAL NULLIF lspzxlh=BLANKS ,
JSRQ DATE "sysdate" --使用系统日期
HSJE INTEGER EXTERNAL NULLIF lspzxlh=BLANKS ,
FPBH INTEGER EXTERNAL NULLIF lspzxlh=BLANKS ,
FPLX INTEGER EXTERNAL NULLIF lspzxlh=BLANKS ,
YXBZ constant 'Y'
)
date 日期型数据没有存储毫秒的地方。
如果想存储毫秒 需要把相应列字段改为 timestamp 类型。
使用sqlloder向date型字段数据导入时,导入的数据精度是到秒级的,导入控制文件中的格式语句例子如下:
LOAD DATA
INFILE 'C:\test.CSV'
APPEND INTO TABLE test
FIELDS TERMINATED BY ","
(ENTRY DATE 'YYYY-MM-DD HH24:MI:SS')
使用sqlloder向date型字段数据导入时,导入的数据精度是到毫秒级的,导入控制文件中的格式语句例子:
LOAD DATA
INFILE 'C:\test.CSV'
APPEND INTO TABLE test
FIELDS TERMINATED BY ","
(ENTRY timestamp 'YYYY-MM-DD HH24:MI:SS.FF3') --毫秒三位精度 如 1998-12-29 21:28:01.000