07 Dec How to include foreign language characters in a .csv import file

Posted at 21:04h in by

Foreign language characters work better with UTF16 encoding.

To prepare .csv files for import users would start with a blank file or an existing export.

If you start with a blank sheet input the foreign characters in the sheet and continue to save as explained below.

If you are opening an existing export file, the foreign characters in the file would have to be corrected before saving. For instance é would display as íš and would have to be corrected.

Excel (Tested in Mac Microsoft Excel ver 14.7.7)

  1. Open the file in Excel
  2. Modify the foreign text to the correct format
  3. Click File>>Save As
  4. Under Format select UTF-16 Unicode Text (.txt)
  5. Click Save
  6. Import the file

Note: if you open this file as-is with Excel, the file will open correctly with the characters displaying correctly. The file is saved with a .txt extension, and has tabs as separators (Microsoft standard functionality)

Excel (Tested in Windows Microsoft Excel 2007)

  1. Open the file in Excel
  2. Click File > Save As
  3. Click the dropdown menu beside Save as File Type and make sure CSV (comma-delimited) is selected
  4. Click the Tools button at the bottom of the window and choose Web Options
  5. Go to the Encoding tab
  6. Under Save this document as, click the dropdown menu and choose Unicode (UTF-8)

OpenOffice (Tested in ver 4.1.5)

  1. Open the file. If it asks which Character set, then select Western Europe (ISO-8859-16/EURO), using Tab and Comma as separators.
  2. Modify the foreign text to the correct format
  3. Click File>>Save As
  4. Under File type: select Text CSV (.csv)
  5. Click on checkbox Edit filter settings
  6. Click Save
  7. Under ‘Export Text File’ dialog change Character set to Unicode (UTF-16)
  8. Under ‘Export Text File’ dialog change Field delimiter to {Tab}. (If this is not done, the file will not open correctly in Excel; excel uses Tab as separator for UTF16 files as a standard)
  9. Click OK
  10. Import the file

Note: this file can now be opened in Excel and all characters should look correct (as long as Tab is the delimiter).

Numbers (Tested in ver 5.3)

  1. Open the file
  2. Modify the foreign text to the correct format
  3. Click File>>Export to>>CSV
  4. Under Text Encoding select Unicode (UTF-16)
  5. Click Next
  6. Click Export

Note: this file will not open correctly in Excel as it is separated by commas and not tabs. You can still use Excel and import data from text file, the characters will still display correctly.