Oracle - SQL Loader cannot load Registered symbol

Question

I had tried numerous settings to load a data file using SQL Loader to the database, but the special characters are not loaded in correctly.

My database is using AL32UTF8 as the character set.

I tried manually run an insert statement to update the table with the special characters and the data is correct.

With the CTL below, i try to load it and the character become a square:

load data
CHARACTERSET 'UTF8'
infile 'data.csv' "str '|\n'"
BADFILE 'data.bad'
DISCARDFILE 'data.dsc'
TRUNCATE
into table data_list
fields terminated by '^' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
data_part,
MFG_NOTES CHAR(800),
MFG_NOTES_TYPE CHAR(800)
)

Any help is appreciated. Thank you.

200+ results searching for [oracle] AL32UTF8, with 16 results for [oracle] AL32UTF8 import. Good luck. — shellter
What character set is your data file encoded using? I would guess it is not UTF-8 — Justin Cave
Thanks @JustinCave , the encoding of the file is the issue. I'll close this question. — ipohfly

pavangulhane pavangulhane · Accepted Answer · 2016-04-29T16:08:27

There is a range of character set encoding that you can use in control file while loading data from sql loader.

LOAD DATA
CHARACTERSET WE8ISO8859P1

or in case of MS word input files with smart characters try in control file

LOAD DATA
CHARACTERSET WE8MSWIN1252

Oracle - SQL Loader cannot load Registered symbol

1 Answers