+ Reply to Thread
Results 1 to 12 of 12

CSV and Unicode (or UTF-8) problem

  1. #1
    TFS
    Guest

    CSV and Unicode (or UTF-8) problem

    In Office 2003 (and I suppose it's true for Office XP and 2000):

    When we save an Excel file to CSV file, Excel doesn't ask if we have to care
    about encoding, in particular, I wanted to save Unicode in UTF-8 but I can't.
    This is very annoying for multi-national companies which has Excel files
    coming from different part of the world.

    On the other hand, I tried to edit a CSV to add some Chinese characters in
    it and save it in UTF-8 and double-click to open it. Excel is then launched
    but it doesn't care file encoding either. The only way to get around
    "opening" is to change the file extension from .csv to .txt. Now, if we open
    this text file, a wizard will present us the option to choose the file
    encoding!!!

    Only a little change in file extension and the behaviour is changed. What a
    pity that Excel doesn't care about UTF-8.

  2. #2
    Dave O
    Guest

    Re: CSV and Unicode (or UTF-8) problem

    In Excel 2k3, one of my Save As options is Unicode. I'm admittedly not
    up to speed on Unicode and UTF-8 but a quick Google review makes me
    wonder: are you mixing your metaphors by saving an XLS file to CSV and
    then desiring it in Unicode format? What happens if you go straight
    from XLS to Unicode?


  3. #3
    TFS
    Guest

    Re: CSV and Unicode (or UTF-8) problem

    What do you mean by "one of my Save As options"??? I have no Unicode
    option (or whatever it suggests is) in my "Save As" window. Just a plain
    Saving window in which there're only the "Filename" and "Save as type" fields
    at the bottom of it. I am afraid the person who made mistake is you, sorry
    to say so. I've checked against several Excel 2K3 in my company and they're
    all the same. No way to specify Unicode (whatever format) while saving CSV.

    "Dave O" wrote:

    > In Excel 2k3, one of my Save As options is Unicode. I'm admittedly not
    > up to speed on Unicode and UTF-8 but a quick Google review makes me
    > wonder: are you mixing your metaphors by saving an XLS file to CSV and
    > then desiring it in Unicode format? What happens if you go straight
    > from XLS to Unicode?
    >
    >


  4. #4
    Dave O
    Guest

    Re: CSV and Unicode (or UTF-8) problem

    Perhaps my mistake was offering to help. Asking again, in a different
    way: if saving from .XLS to .CSV doesn't work in the manner you need,
    will your foreign offices be able to read your file if you save direct
    from XLS format to Unicode format?


  5. #5
    TFS
    Guest

    Re: CSV and Unicode (or UTF-8) problem

    Unicode format of what? You mean CSV file in Unicode (UTF-8) encoding? Well,
    I'd already written in the first post that it's impossible to do so.

    "Dave O" wrote:

    > Perhaps my mistake was offering to help. Asking again, in a different
    > way: if saving from .XLS to .CSV doesn't work in the manner you need,
    > will your foreign offices be able to read your file if you save direct
    > from XLS format to Unicode format?
    >
    >


  6. #6
    Registered User
    Join Date
    11-11-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: CSV and Unicode (or UTF-8) problem

    SOLUTION:
    The only "easy way" of doing this is as follows. First, realize that there is a difference between what is displayed and what is kept hidden in the Excel .csv file.

    (1) Open an Excel file where you have the info (.xls, .xlsx)
    (2) In Excel, choose "CSV (Comma Delimited) (*.csv) as the file type and save as that type.
    (3) In NOTEPAD (found under "Programs" and then Accessories in Start menu), open the saved .csv file in Notepad
    (4) Then choose -> Save As..and at the bottom of the "save as" box, there is a select box labelled as "Encoding". Select UTF-8 (do NOT use ANSI or you lose all accents etc). After selecting UTF-8, then save the file to a slightly different file name from the original.

    This file is in UTF-8 and retains all characters and accents and can be imported, for example, into MySQL and other database programs.

  7. #7
    Registered User
    Join Date
    11-16-2010
    Location
    Indore MP INDIA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: CSV and Unicode (or UTF-8) problem

    No dear, it is not working for me.

    When I save from xls 2003 to csv, the file is having ???? for unicode characters, whether I open it in notepad or wordpad or ms word or excel or anything, even after renaming the extension to txt or whatever.

    Could you recheck that you are really able to retain unicode character when saving to csv format?

    Thanks.
    --
    Rawat

  8. #8
    Registered User
    Join Date
    03-10-2011
    Location
    Ottawa
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: CSV and Unicode (or UTF-8) problem

    Thank you for this solution. This worked great for me needing to insert French characters and making sure it worked with my CMS which only accepts UTF-8

    Quote Originally Posted by coolatron View Post
    SOLUTION:
    The only "easy way" of doing this is as follows. First, realize that there is a difference between what is displayed and what is kept hidden in the Excel .csv file.

    (1) Open an Excel file where you have the info (.xls, .xlsx)
    (2) In Excel, choose "CSV (Comma Delimited) (*.csv) as the file type and save as that type.
    (3) In NOTEPAD (found under "Programs" and then Accessories in Start menu), open the saved .csv file in Notepad
    (4) Then choose -> Save As..and at the bottom of the "save as" box, there is a select box labelled as "Encoding". Select UTF-8 (do NOT use ANSI or you lose all accents etc). After selecting UTF-8, then save the file to a slightly different file name from the original.

    This file is in UTF-8 and retains all characters and accents and can be imported, for example, into MySQL and other database programs.

  9. #9
    Registered User
    Join Date
    09-25-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: CSV and Unicode (or UTF-8) problem

    If you want to solve this within Excel itself, using a macro, see the below:

    http://www.ozgrid.com/forum/showthre...424#post560424

  10. #10
    Registered User
    Join Date
    10-05-2011
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: CSV and Unicode (or UTF-8) problem

    Hi,

    I know it's a bit old thread, but you could use an Excel Addin to solve this issue.

    http://jaimonmathew.wordpress.com/20...h_unicode_csv/

    And it comes with complete source code as well.

    HTH

    Jaimon

  11. #11
    Registered User
    Join Date
    12-15-2011
    Location
    Miami
    MS-Off Ver
    Office 2011 for Mac
    Posts
    1

    Cool Re: CSV and Unicode (or UTF-8) problem

    Open your Excel file with Numbers (part of Apple's iWork suite), then do:
    File, Export, CSV, Unicode (UTF-8).

  12. #12
    Registered User
    Join Date
    04-08-2011
    Location
    cork
    MS-Off Ver
    Excel 2003
    Posts
    5

    Red face Re: CSV and Unicode (or UTF-8) problem

    Hi,
    I understand your problem with this unicode issue. If you download the free "Calc" spreadsheet application from openoffice[dot]org you will be able to save CSV as UTF8 and upload to your database. You can also open excel files with this. I had issues with russian and swedish languages in csv format using excel - basically the text was formatting as ????. The above will solve it. Just save as as UTF8 using Calc in the save process.
    Hope thats helpful!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 4 users browsing this thread. (0 members and 4 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts