2 Replies Latest reply: Nov 9, 2012 2:31 PM by AlbertoFaenza RSS

    External table -Spanish

    970021
      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
          JustinCave
          - 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
            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