This discussion is archived
2 Replies Latest reply: Nov 9, 2012 12:31 PM by AlbertoFaenza RSS

External table -Spanish

970021 Newbie
Currently Being Moderated
I have an external table as described below.
One record of the csv has these values:
104,Referencia de reclamación,ES.
But when I query the table, TP_DESC, here are the values I get:
1,Referencia de reclamaci�S, null.
How do I get it to display the Spanish accent correctly? It looks like the accent is messing up the comma, since 'ES' is not being displayed and looks like it got embedded in reclamaci�S

Please help.


CREATE TABLE TP_DESC
(
P_ID NUMBER(10, 0)
, DESC VARCHAR2(100 BYTE)
, LANG_CD VARCHAR2(2 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR_CONFIG
ACCESS PARAMETERS
(
RECORDS DELIMITED BY '\r\n' SKIP 1
BADFILE DIR_CONFIG_BAD_FILE:'VS_INITIAL_LOAD_BAD.log'
logfile dir_config_log_file:'VS_INITIAL_LOAD_LOG.log'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL
)
LOCATION
(
DIR_CONFIG: 'TP_DESC.csv'
)
)
REJECT LIMIT UNLIMITED;
  • 1. Re: External table -Spanish
    Justin Cave Oracle ACE
    Currently Being Moderated
    - What character set is the data file encoded using?
    - What is the database character set?

    Generally, you probably want your external table definition to specify a CHARACTERSET parameter, i.e.
    RECORDS DELIMITED BY '\r\n' SKIP 1 
    CHARACTERSET <<whatever character set your data file uses>>
    BADFILE DIR_CONFIG_BAD_FILE:'VS_INITIAL_LOAD_BAD.log' 
    Assuming that you specify the correct character set and that your database character set supports the Spanish character in question and that your client application supports the display of the Spanish character, that should work.

    Justin
  • 2. Re: External table -Spanish
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    welcome to the forum.

    Please read SQL and PL/SQL FAQ

    When you put some code please enclose it between two lines starting with {noformat}
    {noformat}
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    You can use CHARACTERSET in external table to specify the character set of your datafile.
    i.e.:
    CREATE TABLE TP_DESC
    (
    P_ID NUMBER(10, 0)
    , DESC VARCHAR2(100 BYTE)
    , LANG_CD VARCHAR2(2 BYTE)
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY DIR_CONFIG
    ACCESS PARAMETERS
    (
    RECORDS DELIMITED BY '\r\n'
    CHARACTERSET WE8MSWIN1252 -- This specifies the character set of datafile
    SKIP 1
    BADFILE DIR_CONFIG_BAD_FILE:'VS_INITIAL_LOAD_BAD.log'
    logfile dir_config_log_file:'VS_INITIAL_LOAD_LOG.log'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL
    )
    LOCATION
    (
    DIR_CONFIG: 'TP_DESC.csv'
    )
    )
    REJECT LIMIT UNLIMITED;
    {code}

    I have used CHARACTERSET WE8MSWIN1252 but you need to check if this one is the one which is the correct one for you.

    Regards.
    Al

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points