Simple CSV files do not support Unicode/UTF-8 characters. This is a limitation of the CSV format and not something that can be changed in DEAR. However, it is possible to import/export Unicode characters following these steps. This article shows the process for Windows machines. Unfortunately, Excel 2016 for Mac is the only version to support this format.
Excel 2016 for Mac only
Unicode/UTF-8 characters include:
- Chinese characters
- any non-Latin scripts (Hebrew, Cyrillic, Japanese, etc.)
- symbols
- barcodes.
NOTE: If you export a file in the correct format and save it as a simple CSV, the characters will be overwritten and CANNOT be reverted to the correct format. If the overwritten file is then imported into DEAR, it will overwrite the characters in the system, and they CANNOT be recovered. This is a limitation of the CSV format that is beyond DEAR's control.
We recommend keeping a backup in Excel format (.xls or .xlsx) with the correctly formatted Chinese characters (or other Unicode characters) in case files from DEAR get overwritten.
If data has been corrupted, there is nothing we can do; the only thing to do is to input the characters again in DEAR, or input the characters in Excel, save to CSV, and import.
Table of Contents
- Exporting CSV files with Unicode characters
- Viewing CSV files with Unicode characters
- Saving an Excel file as CSV with Unicode encoding
- Importing CSV files with Unicode characters
[back to top]
You must first change the General Settings, so export files use the correct encoding.
- Navigate to Settings → General Settings → Organisation.
- Check that Export CSV File Encoding is set to Unicode.
[back to top]
Unicode files CANNOT be opened in Excel by double-clicking the file – the Unicode characters will not display correctly and be damaged. If a file opened in this way is saved, it will overwrite and corrupt the exported file.
You can check that correct details have been exported by opening the file in Notepad. To view Unicode characters in Excel, follow the steps below.
NOTE: MS Excel for Mac 2011 does not open CSV files with Unicode encoding.
View Unicode characters in Excel:
- Open Excel from your menu or Desktop. DO NOT double-click the exported file to open Excel.
- Navigate to Data → Get External Data → From Text.This may look a bit different depending on which version of Excel you are running.
- Navigate to the location of the CSV file you want to import.
- Choose the Delimited option.
- Set the character encoding File Origin to 65001: Unicode (UTF-8) from the drop-down list.
- Check My data has headers so that Excel recognises that the first row of the CSV file has column names.
- Click Next to display the second step of Text Import Wizard.
- Set the delimiter to Comma.
- Click Next to move on to the next step.
- Select all columns and setColumn dataformat to Text.
- Click OK and then Finish.
- Keep the default values inside the Import Data dialogue and click OK.
- Now you can make modifications to the file and save the file as an Excel document or a CSV file.
[back to top]
In some cases, you will be working with a new spreadsheet, not one that was exported from DEAR. You will need to save your Excel spreadsheet as a CSV file with unicode encoding in order to import it correctly into DEAR without corrupting the special characters.
The steps are as given below:
- Open an Excel file with Microsoft Excel.
- DO NOT double-click on a CSV file exported from DEAR to open it in Excel - it will corrupt any Unicode characters.
- Navigate to File → Save As.
- Navigate to Browseto select a location to save the file.
- The Save As window appears as shown below.
- Next, enter the name of the file.
- Select the Save as type as CSV (Comma delimited) (*.csv)option.
- Click Tools drop-down box and click Web Options.A new window for web options appears.
- Under Encoding tab, select the option Unicode (UTF-8) from Save this document asdrop-down list.
- Finally, click OK,and save the file.
[back to top]
The final step is to import the modified file back into DEAR. You can import the file from the same screen that you exported from.
NOTE: You must only use the CSV file that you have JUST saved from the Excel file. If you open the CSV file, make changes, then save it again, this will remove all the UTF/Unicode characters. If you want to make changes, you must make them in the Excel file.
If a file with corrupted Unicode/UTF-8 characters is uploaded to DEAR, it will corrupt the data in DEAR, and there is nothing we can do to restore it. The only thing to do is to input the characters again in DEAR, or input the characters in Excel, save to CSV, and import.
