2

Before you tell me to search more and to look at other people's questions and their answers read my precise question.

Most other topics here deal with how to make UTF-8 the default option within the text import wizard. However, I would like to avoid having to use the text import wizard at all.

I have CSV files that I want to open by double clicking. Excel opens them automatically, but even if I did the regedit trick of using 65001 (UTF-8) as the default encoding, the files are still not correctly displayed.

Is there some way to force this encoding in the opening of a file instead of having to import it?

Thanks in advance

(using Excel 2016 in US English and my Windows 7 regional configuration is set to Spanish (United States), I don't know if that is important, but it actually was for dealing with default commas as separators)

EDIT for clarification: accents and characters from other languages still appear as weird codes when opening a CSV file after doing the regedit trick of forcing utf-8 for the import. I am not importing though (the trick works as expected there); I am opening the file directly.

Nicolás Fernández Fioretti
  • 23

1 Answers1

3

You can use a PowerShell script to open CSV files and automatically pass them to Excel. The script silently uses Excel's text import method which handles UTF-8 encoding and as a bonus treats values always as text

Put a shortcut to this script in your sendto folder so you can open all CSV files via Right click » SendTo » myScript (easy method)

(or)

You can use PS2EXE to convert your script to an executable (.exe). Now you can right click a .csv file and under 'Open with' you choose this .exe as your default program to open CSV files (advanced method)

What it does

  • UTF-8 characters are displayed correctly
  • Can open multiple CSV files. Stores each CSV content as new worksheet in the same workbook

  • All values are treated as pure text. No interpretation from Excel's buildin CSV handler

    • ie. 0001 stays 0001 and is not converted to 1
    • ie. -A122:23 or =AZ1+32 are not interpreted as formula and stay as they are

How to use

  • Create a new text file and paste the below script. A commented version can be found here
$CSVs = @()
$args.ForEach({
    If ((Test-Path $_) -and ($_ -Match ".csv$|.txt$")) {
        $CSVs += ,$_
    } 
})

if (-Not $null -eq $CSVs) {

    $excel = New-Object -ComObject excel.application 
    $excel.visible = $true
    $excel.SheetsInNewWorkbook = $CSVs.Count    
    $workbook = $excel.Workbooks.Add()

    for ($i=0; $i -lt $CSVs.Count; $i++){

        $csv = Get-Item $CSVs[$i]
        $worksheet = $workbook.worksheets.Item($i + 1)
        $worksheet.Name = $csv.basename

        $TxtConnector = ("TEXT;" + $csv.fullname)
        $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
        $query = $worksheet.QueryTables.item($Connector.name)
        $query.TextFilePlatform = 65001
        $query.TextFileTextQualifier = 1
        $query.TextFileOtherDelimiter = $Excel.Application.International(5) 
        $query.TextFileParseType  = 1
        $arrFormats = ,2 * $worksheet.Cells.Columns.Count
        $query.TextFileColumnDataTypes = $arrFormats
        $query.AdjustColumnWidth = 1
        $query.Refresh()
        $query.Delete()
    }
}

  • Save it somewhere like C:\Tools\myScript.ps1. (Note the extension .ps1)
  • Open your sendto folder via WinR » shell:sendto » Enter
  • Create a new shortcut via Right click » New » Shortcut and paste this line. Don't forget to change the path to your own one where you've put your script

"%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe " -NoProfile -NonInteractive -WindowStyle Hidden -File "C:\my\folder\myScript.ps1"

Comparison

  • New way: Opened via script (either through sendto or PS2EXE converted executable)

enter image description here

  • Old way: Opened via double click

enter image description here

Remarks

  • PS2EXE does not run on Windows 10 because the .NET version 4.5 or higher is not supported from PS2EXE. The program works on Windows 7 with .NET 4.0

  • In my first tests Excel did show gibberish when trying to display chinese UTF-8 characters. However during my tests this behavior switched and now both methods handle UTF-8 correctly. I have no idea what caused this

nixda
  • 27,634