Exporting Records With Special Characters

Overview

Sugar® allows users to export data to a CSV file directly from the module's list view or from a rows-and-columns report. When records in your Sugar database contain special characters (e.g. é, à, ú, and ð), the exported CSV file may not display the characters correctly. This article covers how to properly define the export character set in Sugar so that special characters display correctly in the CSV file.

For more information on exporting data from Sugar, please refer to the Export documentation. 

Use Case

Customers who use special characters like é, à, ú, ð, etc. in Sugar may notice that these characters do not display correctly after exporting to a CSV file. Typically, this is due to the spreadsheet application misinterpreting the characters. For example, in Sugar the special character é displays correctly in the account record's Name field:

However, when the record is exported to a CSV file and opened in Microsoft Excel®, the character renders incorrectly:

This happens because the character set that Sugar used to encode the letters on export does not match the character encoding expected by Excel. To learn more about character encoding, check out the Character Encoding section at the bottom of this page. To fix the encoding issue for your export, continue reading the Steps to Complete.

Steps to Complete

In this example, we have some existing records in Sugar with special characters in the fields we plan to export. We will configure the default character set to ISO-8859-9 via Admin > Locale so that the special characters display properly when exported to the CSV file. Please note that we chose ISO-8859-9 because it best suits this case's specific encoding needs but it may not be the best character set for you. For more information on the available encoding sets, please refer to the Character Encoding section at the bottom of this page.

Configuring the Character Set

These steps cover an administrator changing the character set for the entire instance. Users can also change the default character for their own user account by configuring the Import/Export Character Set field in their user profiles. For more information on changing the default character set for individual users, please refer to the Getting Started documentation. 

Use the following steps to change the default character set system-wide in Sugar:

1. Navigate to Admin > Locale.
2. Select "ISO-8859-9" in the Default Character Set for Import and Export field.
   • The ISO-8859-9 character set will properly display various types of special characters in Microsoft Excel® on both Windows and Mac.
   • Note: The default character set also applies to data and vCard imports in Sugar. For more information on importing and vCard generation, please refer to the Import documentation and the Contacts or Leads documentation for vCards.
3. Click "Save" to preserve the changes.
   

Application

Once the export character set has been set to ISO-8859-9, navigate to the Accounts list view and select the appropriate record(s) you wish to export. You can also create a Rows and Columns-type report for the Accounts module and export the file as well. For more information on creating and exporting reports, please refer to the Reports documentation. 

For this example, we will export the México Import/Export Company record from the Accounts list view. The CSV file will now display the account name correctly using the new character set configured above.

Character Encoding

The initial miscommunication between Sugar and Excel is due to the globalization of character sets for computer systems. Because different regions of the world utilize unique characters in their alphabets, many different character sets exist. While some character sets focus on covering characters present in Western languages, others have been developed to specifically handle characters found in, for example, the Japanese alphabet (e.g. SJIS) or Eastern European alphabets (e.g. KOI).

For more information on global character sets and a list of common character encodings, please refer to this page on Character Encodings from wikipedia.org. If you find that your special characters continue to display incorrectly using ISO-8859-9, try one of the different export character sets listed on the Wikipedia page, again following the steps in the Steps to Complete section above.