none
Excel is recoding UTF-8 CSVs as ANSI! RRS feed

  • Question

  • Our CSVs are UTF-8 encoded - they need to be opened in Excel, more data inputted, and saved (to be imported back into the system they came from). The problem is as stated in the title - when the file is saved the encoding is changed to ANSI, rendering the file incompatible for the import process.

    I've seen several workarounds offered - namely:

    • Rinse the file through Google Docs
    • Open the file in Notepad and choose "Save as", then set the encoding back to UTF-8
    • Use the 'import data' function in a new workbook, where you can point at the original file and choose the encoding in the import
    • Don't use Excel

    None of these are a good enough solution - not for a large number of end users with mixed digital capabilities, and nor is it good enough from Microsoft!

    So my questions are:

    1. Why is this happening?
    2. Why does my version of Excel 2016 (v1801 on O365 ProPlus) offer a Save As file type of "CSV UTF-8 (comma delimited)", whereas none of the staff imaged machines running Excel 2016 (non-o365) have this option?

    Help would be gratefully appreciated!

    Wednesday, February 7, 2018 7:30 PM

All replies

  • Hi,

    Excel opens in ANSI format and does not do code recognition, this is by design behavior. 

    If you want to open UTF-8 csv directly in Excel, I suggest you add BOM to UTF-8 files first.

    Hope it's helpful.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, February 8, 2018 10:04 AM
    Moderator
  • Hi Emi

    Thanks for getting back to me.

    I'm afraid adding BOM is not an option - they system that originally generates the CSV requires it to stay 'as is' (i.e. UTF-8 without BOM) so the file can be re-imported. And we're also talking about end-users performing this 'export - amend - import back into the system' process, so it needs to be an end-user friendly solution.

    I could reasonably ask all staff to ensure they used the File > Save As > choose file type "CSV UTF-8 (comma delimited)" approach... if they had this option. But they don't, so I need to know the answer to question 2:

    Why does my version of Excel 2016 (v1801 on O365 ProPlus) offer a Save As file type of "CSV UTF-8 (comma delimited)", whereas none of the staff imaged machines running Excel 2016 (non-o365) have this option?

    Thanks for your continuing assistance (and to any others that can also advise here)

    Thursday, February 8, 2018 10:32 AM
  • Hi,

    I find if you want to save CSV as UTF-8 encoding your Office build version must equal to or later than 16.0.7466.2023. 

    And this version of 16.0.7466.2023 is for Office 365.

    And the different between Office 365 and Office 2016 with key, Office 365 will receive feature updates while Office 2016 will.

    This might be the reason of this problem.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, February 9, 2018 9:48 AM
    Moderator