Categories
Development

Open CSV file of UTF-8 encoding in Excel

Working with generated UTF-8 files it’s a challenge to view them properly in Excel. Gladly there is a method how to properly open them in Excel.

  1. Choose File -> New and select a blank template.
  2. Choose Data tab.
  3. Then click From Text option at the Get External Data section. Select a CSV file.
  4. Excel will display “Text Import Wizard”.
  5. Step 1 of the Wizard
    • Select File type by choosing “Delimited” radio button.
    • In the File Origin dropdown select the UTF-8 encoding: “65001 : Unicode (UTF-8)”.
    • Click Next button.
  6. Step 2 of the Wizard
    • Choose field Delimiter based on what delimiter is written/used in the CSV file. Semicolon delimiter fits best for EU formatting while Comma delimiter is best for US formatting.
    • Text Qualifier option depends again on the CSV file. See more below.
    • Click Next button.
  7. Step 3 of the Wizard – click Finish button.
  8. You’ll be prompted to choose where to put new data:
    (1) Existing worksheet or (2) New worksheet.
  9. Choose an appropriate option and press OK button.

Text Qualifier

The Excel Text Qualifier is allowed as only 3 options: "' or {none}

One may escape [double]quotes in Excel by doubling them in the original CSV. Example a CSV code:

"01","text","<html><tag attrib=""true"">"

will issue in the following proper result:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.