0

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)"
)
KLeonine
  • 167
  • 2
  • 10
  • Please refer to the question, I modified it to have csv and ctl samples and the datatype. – KLeonine Jan 30 '14 at 19:46
  • 2
    What character set is the CSV file encoded using? – Justin Cave Jan 30 '14 at 20:10
  • 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 Jan 30 '14 at 20:53
  • 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 –  Jan 30 '14 at 21:59

2 Answers2

1

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?

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
  • The CHARACTERSET parameter is for the character set of the [data file](http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#SUTIL10870)... so this is only of use if the data file is in fact in UTF8. Judging by the problems I'd guess it's _not_.... – Ben Jan 30 '14 at 19:52
  • Okay. Just a guess. Have seen this kind of problems a lot. Will check this out and come back to you. – Patrick Hofman Jan 30 '14 at 19:53
  • So it was the file encoding anyway. Glad you found the issue. – Patrick Hofman Jan 30 '14 at 22:41
  • Thanks everyone for the help. – KLeonine Jan 31 '14 at 14:37
1

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

KLeonine
  • 167
  • 2
  • 10