Loading currency symbols using SQL*Loader

0
votes

I am trying to load a csv file with currency symbols, using SQL*Loader. The symbol '£' gets replaced by '#' and symbol '€' gets replaced by NULL.

Not sure if I should tweak some settings in my control file?

Here are the values from NLS_DATABASE_PARAMETERS: 

NLS_NCHAR_CHARACTERSET = AL16UTF16  
NLS_CHARACTERSET = AL32UTF8

Any pointers would be of great help.

Extract of csv file - 

id,currency
1234,£
5678,€

Datatype of the column for currency is NVARCHAR2(10).

Here's the ctl file - 

OPTIONS(skip=1)
LOAD DATA
TRUNCATE
INTO TABLE schema_name.table_name
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
filler1 filler,
filler2 filler,
Id INTEGER EXTERNAL,
Currency CHAR "TRIM(:Currency)"
)
2
oraclecurrencysql-loader
Please refer to the question, I modified it to have csv and ctl samples and the datatype. - KLeonine
What character set is the CSV file encoded using? - Justin Cave
Not sure, client sent the xlsx, I copy-pasted the contents into a new file (using MS Excel) and saved it as comma delimited (*.csv) file. Another problem I am facing because of this is, there is CR and LF appended at the end of each line. I can replace them by NULL after the data is loaded in oracle table. - KLeonine
I'm not sure if NLS_DATABASE_PARAMETERS is all you need to check. You also need to check the NLS session settings. For SQL*Loader this would be the environment variables NLS_XXX - a_horse_with_no_name

2 Answers

1
votes

I guess this is a character set problem.

Did you set the character set of the sqlloader file to UTF8?

CHARACTERSET UTF8

Also, is the file itself UTF8 encoded?

1
votes

Thanks Justin and Patrick for the pointer!

The file was not UTF-8 encoded. I converted the file to UTF-8 encoding and it worked!

For those who don't know how to convert the file's encoding using Notepad++ (like me, I just learned how to do it) : Create a new file in Notepad++ -> Go to Encoding -> Encode in UTF-8 -> Copy-paste the contents -> save the file as .csv