1 2 Previous Next 26 Replies Latest reply: Feb 18, 2013 11:15 PM by Senthilkumar S RSS

    CSV file <Â>

    Senthilkumar S
      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
          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
            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
              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 <Â>
                Senthilkumar S
                Hi ranit B,

                Am using MS Excel 2010 to open CSV file.

                Can we fix this?
                • 5. Re: CSV file <Â>
                  ranit B
                  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
                    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 <Â>
                      Senthilkumar S
                      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
                        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
                          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 <Â>
                            Senthilkumar S
                            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
                              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 <Â>
                                Senthilkumar S
                                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
                                  1st row. 1st col

                                  Just give a try...
                                  • 14. Re: CSV file <Â>
                                    Senthilkumar S
                                    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