This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Feb 18, 2013 9:15 PM by SenthilkumarS RSS

CSV file <Â>

SenthilkumarS Newbie
Currently Being Moderated
Hi,

while creating CSV file from data retrieved from cursor, We are getting some of data are added with  but its not available in database.

EX: -20º to 70ºC is coming into CSV file as -20º to 70ºC

How to remove this?

Edited by: 925896 on Feb 11, 2013 1:21 AM
  • 1. Re: CSV file <Â>
    BluShadow Guru Moderator
    Currently Being Moderated
    What is the international/nationality settings of your client and database? What character set is being used by the database?
  • 2. Re: CSV file <Â>
    odie_63 Guru
    Currently Being Moderated
    925896 wrote:
    EX: -20º to 70ºC is coming into CSV file as -20º to 70ºC
    In UTF-8, the degree symbol is encoded on two bytes (0xC2B0). Taken separetely, those two bytes correspond to character "Â" (C2) and character "°" (B0).
    Most likely, the file has been written using UTF-8 encoding but your text editor doesn't support multibyte characters, that's why you see two characters instead of one.
  • 3. Re: CSV file <Â>
    ranit B Expert
    Currently Being Moderated
    925896 wrote:
    Hi,

    while creating CSV file from data retrieved from cursor, We are getting some of data are added with  but its not available in database.

    EX: -20º to 70ºC is coming into CSV file as -20º to 70ºC

    How to remove this?
    1. How are you creating the CSVs?
    2. Do you have BOM Byte Order Mark as the first line of the CSV?
    Read here - http://en.wikipedia.org/wiki/Byte_order_mark

    Please clarify these.

    Not sure in this case ,but has helped me previously... use this as the first line of your CSV (it'll be invisible if properly inserted) :
     CHR(0xFEFF)
    Ranit B.

    Edited by: ranit B on Feb 11, 2013 3:38 PM
    --- CHR added... please try this.
  • 4. Re: CSV file <Â>
    SenthilkumarS Newbie
    Currently Being Moderated
    Hi ranit B,

    Am using MS Excel 2010 to open CSV file.

    Can we fix this?
  • 5. Re: CSV file <Â>
    ranit B Expert
    Currently Being Moderated
    Check a suggestion above(in my last reply)... please try it.
    Am using MS Excel 2010 to open CSV file.
    I'm not asking about opening it. Tell me how is it getting populated / generated ??

    Is it any Cursor loop and Print statements that does the work??
    Please clarify.
  • 6. Re: CSV file <Â>
    odie_63 Guru
    Currently Being Moderated
    Answer BluShadow's questions too. Database character set?

    If you know the encoding of the file, then you can run Excel's text importer utility and choose the correct encoding.

    If your database charset is AL32UTF8, then indeed Ranit's suggestion about the BOM may solve the problem too, though I would rather use this sequence :
    utl_raw.cast_to_varchar2(hextoraw('EFBBBF'))
  • 7. Re: CSV file <Â>
    SenthilkumarS Newbie
    Currently Being Moderated
    Am appending data as commas separated into CLOB variable from cursor.

    Also I have used dbms_xslprocessor.clob2filE to write the file from CLOB.
  • 8. Re: CSV file <Â>
    ranit B Expert
    Currently Being Moderated
    Hi Odie,

    Could you please explain us the concept of the Database character sets and how are the foreign characters depend on them??
    These are always an head-ache.
    utl_raw.cast_to_varchar2(hextoraw('EFBBBF'))
    What is this exactly you trying to do here??? the concept?
  • 9. Re: CSV file <Â>
    stefan nebesnak Journeyer
    Currently Being Moderated
    ranit B wrote:

    Could you please explain us the concept of the Database character sets and how are the foreign characters depend on them??
    The database character set determines the set of characters can be stored in the database.
    It is also used to determine the character set to be used for object identifiers and PL/SQL variables and for storing PL/SQL program source.

    The database character set information is stored in the data dictionary tables named SYS.PROPS$.
    SQL> select value$ from sys.props$ where name='NLS_CHARACTERSET';
     
    VALUE$
    --------------------------------------------------------------------------------
    EE8ISO8859P2
    You can use CONVERT function to convert a string from one character set to another..
    SQL> SELECT CONVERT('°','AL32UTF8','EE8ISO8859P2') val FROM DUAL;
     
    VAL
    ---
    °
     
    SQL> SELECT CONVERT('°','EE8ISO8859P2','AL32UTF8') val FROM DUAL;
     
    VAL
    ---
    °
    ---------------
    ranit B wrote:
    utl_raw.cast_to_varchar2(hextoraw('EFBBBF'))
    What is this exactly you trying to do here??? the concept?
    EF BB BF is hexadecimal representation of byte order mark (BOM) for UTF-8 Encoding, it allows the reader to identify the file as an UTF-8 file.

    With above statement you can write (BOM) at the beginning of the CLOB (in case you have AL32UTF8 character set encoding).

    When casting to a VARCHAR2, the current Globalization Support character set is used for the characters within that VARCHAR2.
    SQL> select utl_raw.cast_to_varchar2(hextoraw('C2B0')) val from dual;
     
    VAL
    --------------------------------------------------------------------------------
    °
    
    SQL> select utl_raw.cast_to_varchar2('B0') from dual;
     
    UTL_RAW.CAST_TO_VARCHAR2('B0')
    --------------------------------------------------------------------------------
    °
  • 10. Re: CSV file <Â>
    SenthilkumarS Newbie
    Currently Being Moderated
    Character set is AL32UTF8...

    When I retrieve data ,its coming properly. But after writing as CSV file, am getting this format issue
  • 11. Re: CSV file <Â>
    ranit B Expert
    Currently Being Moderated
    925896 wrote:
    Character set is AL32UTF8...

    When I retrieve data ,its coming properly. But after writing as CSV file, am getting this format issue
    Like I said previously, try printing the BOM character as the first line of your CSV (the same way how you are printing the other details)
    CHR(0xFEFF)
    Please let us know if that helps...
    Ranit B.
  • 12. Re: CSV file <Â>
    SenthilkumarS Newbie
    Currently Being Moderated
    Hi Ranit,

    DO you want me to add CHR(0xFEFF) for each column of first row?
    OR just for first column of first row?
  • 13. Re: CSV file <Â>
    ranit B Expert
    Currently Being Moderated
    1st row. 1st col

    Just give a try...
  • 14. Re: CSV file <Â>
    SenthilkumarS Newbie
    Currently Being Moderated
    When I try to add as first row
     l_contents:=CHR(0xFEFF) || CHR(10);
    here l_contents is clob

    am getting below error


    ORA-06550: line 138, column 18:
    PLS-00103: Encountered the symbol "XFEFF" when expecting one of the following:

    ) , * & = - + < / > at in is mod remainder not rem =>
    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
    like4 likec as between from using || multiset member
    submultiset
    The symbol "," was substituted for "XFEFF" to continue.
    ORA-06550: line 143, column 32:
    PLS-00103: Encountered the symbol "WITH" when expecting one of the following:

    ( select
1 2 Previous Next

Legend

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