none
Import of Unicode CSV files RRS feed

  • Question

  • I observe an issue in Excel 2013 and 2016. It fails to recognize Unicode files and doesn't split the columns with the defined delimiter. Whenever the Byte Order Mark (the first two bytes) is 0xFF 0xFE, a complete line is pasted in one cell after double-clicking the CSV file in Explorer.

    Let's say, the system list separator is ";". Create a text file in notepad with the following lines:

    x;y;z
    1;2;3
    4;5;6

    Save it in ANSI format, UTF-8 and Unicode with the csv extension and double-click the files. The result is:

    ANSI: Columns separated correctly (A1:x, B1:y, C1:z, A2:1, B2:2, C2:3 ...)
    UTF-8: Columns separated correctly
    Unicode: x;y;z in A1, 1;2;3 in A2, 4;5;6 in A3
    Unicode with removed first 2 bytes in a hex editor: Columns separated correctly

    It works, when I add SEP=; as the first line of the Unicode file, but this is not an option, because we have an application that offers to open a created csv file in Excel.

    I know, this is the wrong platform to address this issue, but maybe some developer at Microsoft who reads this can fix this little bug in one of the next patches.

    In the meantime there are different workarounds available. I could even write an Add-In that looks something like this: https://jaimonmathew.wordpress.com/2011/08/23/excel_addin_to_work_with_unicode_csv/


    Monday, May 22, 2017 1:25 PM

All replies

  • Hi Alexander Freudenberg,

    I've tried,but could not reproduce your issue. I'm using Windows 10 Pro 64-bit (Creators Update) and Excel 2016 32-bit (latest version/build).

    When we double click a CSV file and open Excel (by file association), Excel does not recognize semicolon(;) in data as a delimiter.
    Do I misunderstand something?
    ___________
    Ashidacchi
    Monday, May 22, 2017 11:10 PM
  • You need to change the list separator in the regional settings of the control panel first. Or you can use the comma as a separator in your test.
    Tuesday, May 23, 2017 5:06 AM
  • Hi Alexander,

    I changed the system list separator to ";" via control panel and can reproduce this in my Excel 2016 (16.0.8201.2025, 64-bit):

    Currently I am not sure if this is an expected behavior or a limitation of Excel as I did much research on this but cannot find documentation stating this. Personally, I'd like to encourage you submit the feedback via Excel UserVoice forum. Microsoft loves hearing from all customers and is committed to continuously improving your experience with the products :)

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, May 24, 2017 2:18 AM
    Moderator