how to upload special characters with sql loader

Question

I have a file with special characters. How do I have to activate the encoding to allow uploading this information.

file.txt

11|campo2|09/13/2017 11:36:11 PM
12|cáéíóúñ12345aeiou|09/22/2017 02:41:38 PM

select * from nls_session_parameters;

ControlFile.ctl

OPTIONS (SILENT=(ALL, HEADER))
load data
infile file.txt
badfile file.bad
discardfile file.dis
append
into table TABLE_INFO
fields terminated by '|'
TRAILING NULLCOLS (
  SUB_ID INTEGER EXTERNAL,
  SUB_PROPERTY CHAR(4000),
  MODIFY_TIME DATE TERMINATED BY "|" "to_date(:MODIFY_TIME, 'MM/DD/YYYY HH:MI:SS PM')",
  UPLOAD_DATE DATE TERMINATED BY "|" "to_date('20201215153045', 'YYYYMMDDHH24MISS')",
  FILE_NAME constant "nombre_archivo.txt"
)

Table

create table TABLE_INFO
(
  sub_id       NUMBER(20) not null,
  sub_property VARCHAR2(4000),
  modify_time  DATE,
  upload_date  DATE,
  file_name    VARCHAR2(200)
)

sqlldr userbd/passbd control=ControlFile.ctl log=log_file.log rows=10000 direct=true

Please don't paste images, provide result as formatted text (https://www.convertcsv.com/csv-to-flat-file.htm or https://senseful.github.io/text-table/ may help) — Wernfried Domscheit, Dec 04 '20 at 08:31
`NLS_SESSION_PARAMETERS` is useless - your `sqlldr` will run in a different session, it does not show the **database** character set and no view shows your `NLS_LANG` parameter — Wernfried Domscheit, Dec 04 '20 at 08:33

Wernfried Domscheit · Answer 1 · 2020-12-04T09:04:32.977

There are three points you have to consider:

Ensure that your database is able to store such characters. This can be interrogated by
```
SELECT * 
FROM V$NLS_PARAMETERS 
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET')
```
View NLS_DATABASE_PARAMETERS will also work
On your client use a character set which supports such characters. On Windows you could do for example chcp 65001 in order to set UTF-8
Tell the Oracle database which character set is used by your client. This is typically done by the NLS_LANG parameter, e.g. SET NLS_LANG=SPANISH_SPAIN.AL32UTF8

In your sqlldr control file you can set also

CHARACTERSET character_set_name

which takes precedence over NLS_LANG character set value (see Data File Character Sets).

See also OdbcConnection returning Chinese Characters as "?"

NB, for proper import of DATE values the field should be specified like

MODIFY_TIME DATE 'MM/DD/YYYY HH:MI:SS PM'

how to upload special characters with sql loader

1 Answers1

Linked