Oracle SQL加载器-双Qutoes...

askTom 2017-05-16

238

问题描述

嗨，

我有以下格式的数据，如何在不丢失double的情况下加载它...

所有列以 “” 括起来并用〜分隔，最后一列的值也带有双引号，需要用引号加载

"Jobid" ~ "Jobname" ~ "状态" ~ "注释"
"Test1" ~ "Test1作业" ~ "完成" ~ "作业完成无任何问题"
"Test2" ~ "Test2作业" ~ "进行中" ~ "作业依赖于" test0 "作业状态"
“Test3” 〜 “Test3作业” 〜 “进行中” 〜 “〜” 作业将从 “下周” 开始
"Test4" ~ "Test4作业" ~ "完成" ~ "作业完成无任何问题"

专家解答

像这样的怎么样

SQL> create table t (
  2  Jobid varchar2(20),
  3  Jobname varchar2(30),
  4  Status varchar2(30),
  5  Comments varchar2(30)
  6  );

Table created.

LOAD DATA
INFILE *
TRUNCATE 
INTO TABLE t
FIELDS TERMINATED BY "~" 
TRAILING NULLCOLS
(
Jobid "LTRIM(RTRIM(:Jobid,chr(34)),chr(34))",
Jobname "LTRIM(RTRIM(:Jobname,chr(34)),chr(34))",
Status "LTRIM(RTRIM(:Status,chr(34)),chr(34))",
Comments "LTRIM(RTRIM(:Comments,chr(34)),chr(34))"
)
BEGINDATA
"Test1"~"Test1 Job"~"Complete"~"Job completed without any issues"
"Test2"~"Test2 Job"~"In-progress"~"Job has dependency with "test0" job status "
"Test3"~"Test3 Job"~"In-progress"~"Job will start from "Next weekFrdiay" "
"Test4"~"Test4 Job"~"Complete"~"Job completed without any issues"


SQL> select * from t;

JOBID                JOBNAME                        STATUS                         COMMENTS
-------------------- ------------------------------ ------------------------------ ----------------------------------------------
Test1                Test1 Job                      Complete                       Job completed without any issues
Test2                Test2 Job                      In-progress                    Job has dependency with "test0" job status
Test3                Test3 Job                      In-progress                    Job will start from "Next weekFrdiay"
Test4                Test4 Job                      Complete                       Job completed without any issues
复制