I have a flat file with the following record format:
2017-05-01 17:12:28ÿAuth_Referralÿ71631ÿ6803448.70
Notice the field sep is a small y with 2 dots above it. This is windows extended ascii for FF, or 255. I'm unable to designate that character as my field terminator as follows, but if I use a normal delimiter, like a "|", it works fine:
This works fine:
(CREATE_DATE DATE,
TABLE_NAME VARCHAR2(30),
RECORD_COUNT VARCHAR2(10),
TOTAL_AMOUNT NUMBER(10,2))
ORGANIZATION external
(TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE 'authsmry.bad'
LOGFILE 'authsmry.log'
READSIZE 1048576
FIELDS TERMINATED BY '|' LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(CREATE_DATE CHAR(19) date_format DATE 'yyyy-mm-dd-HH24.MI.SS',
TABLE_NAME CHAR(30),
RECORD_COUNT CHAR(10),
TOTAL_AMOUNT CHAR(13))
)
location
('authsmry')
)REJECT LIMIT UNLIMITED;
But when I try FIELDS TERMINATED BY 'ÿ'
or FIELDS TERMINATED BY 0X'FF'
or FIELDS TERMINATED BY 0X'255'
etc. it won't recognize the field terminator.
I have looked all over for a solution to this problem including in your database, but no joy.
Please help,
Regards,
Gary
The character ÿ has different encodings depending on the character set!
https://www.compart.com/en/unicode/U+00FF Explicitly specify the character set in the access parameters and it should work:
create table t (
CREATE_DATE varchar2(19),
TABLE_NAME VARCHAR2(30),
RECORD_COUNT VARCHAR2(10),
TOTAL_AMOUNT NUMBER(10,2)
) ORGANIZATION external
(TYPE oracle_loader
DEFAULT DIRECTORY tmp
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
CHARACTERSET 'UTF8'
FIELDS TERMINATED BY 'ÿ' LRTRIM
REJECT ROWS WITH ALL NULL FIELDS (
CREATE_DATE CHAR(19) ,
TABLE_NAME CHAR(30),
RECORD_COUNT CHAR(10),
TOTAL_AMOUNT CHAR(13)
)
)
location ('test.txt')
) REJECT LIMIT UNLIMITED;
select * from t;
CREATE_DATE TABLE_NAME RECORD_COUNT TOTAL_AMOUNT
2017-05-01 17:12:28 Auth_Referral 71631 6,803,448.7