Accents not showing up properly in Excel on Windows

jprince-cds commented 3 months ago

Description

When opening a CSV file that contains French accents (questions and/or responses), they don't show up properly on Windows. The accents get converted to 2 other symbols. The reason for this is the file is in Unicode format, while Windows Excel seems to be expected ANSI/iso-8859-1 encoding.

Steps to reproduce

On a Windows PC, double-click a CSV file containing responses with French accents.
You will notice that accents are displayed incorrectly, for example é becomes Ã©

Details

Browser: Any
Operating system: Windows
Environment: Production
Language: English
Viewport: Any

Expected behaviour

Accents should be displayed correctly.

Here is the workaround needed for the files to open up correctly in Excel on Windows.

Right-click the CSV file, Open With, choose Notepad. Select File, Save as... In the save dialog, change the encoding to ANSI. Save the file and double-click to open in Excel. The accents are loading properly.

Screenshots or videos

jprince-cds commented 3 months ago

testing-sign-up-form-2024-06-11-responses-reponses.csv.zip

Zipped version of a CSV file containing accents. (was not able to upload a CSV file directly to GitHub)

jprince-cds commented 3 months ago

We should inform client in Ticket 17757 once this is resolved, as they are using this workaround now.

anikbrazeau commented 1 month ago

Related to #3878

timarney commented 1 month ago

How often are we seeing support requests for this type of issue come up?

Is this happening for all / most users or a few?

For now I found a couple of alternative ways to get excel to pick up the utf-8 encoded files properly at the file level vs making OS level changes.

1) Data import

import

2) Rename the .csv extension to .txt

Open the .txt file in Excel

Excel will prompt with import step

txt

timarney commented 1 month ago

RE: converting to a .txt file

Found this note under the Excel help docs

Note: When Excel opens a .csv file, it uses the current default data format settings to interpret how to import each column of data. If you want more flexibility in converting columns to different data formats, you can use the Import Text Wizard. For example, the format of a data column in the .csv file may be MDY, but Excel's default data format is YMD, or you want to convert a column of numbers that contains leading zeros to text so you can preserve the leading zeros. To force Excel to run the Import Text Wizard, you can change the file name extension from .csv to .txt before you open it, or you can import a text file by connecting to it (for more information, see the following section).

timarney commented 1 month ago

Found this Microsoft article

You can open a CSV file encoded with UTF-8 normally if it was saved with BOM (Byte Order Mark)

You can open a CSV file encoded with UTF-8 normally if it was saved with BOM (Byte Order Mark). Otherwise, you can open it through either of the following ways.

https://support.microsoft.com/en-us/office/opening-csv-utf-8-files-correctly-in-excel-8a935af5-3416-4edd-ba7e-3dfd2bc4a032

Testing with the file attached to this issue

There is no - Byte order mark (BOM)

https://validator.w3.org/i18n-checker/check#validate-by-upload+

Ref: https://github.com/cds-snc/platform-forms-client/pull/2659

timarney commented 1 month ago

https://github.com/cds-snc/platform-forms-client/pull/4237

Testing here https://o52ubyux2llkeflxz3qkqbbs4i0yzada.lambda-url.ca-central-1.on.aws

Created a form

Added response --- download CSV

Uploaded to checker the file is showing with Byte order mark (BOM)

Tested on tablet --- opened with chars showing.

timarney commented 1 month ago

@Abi-Nada @anikbrazeau

Are you able to generate a response file that doesn't have a Byte order mark (BOM) ?

I tested under a fresh PR and on staging (no updates) and the files have the Byte order mark which per note open on my tablet fine vs the file attached to this issue doesn't (and doesn't have the BOM).

You can check a file to see if it has a BOM by uploading a file here: https://validator.w3.org/i18n-checker/check#validate-by-upload+

i.e detects BOM as UTF-8