Open CSV UTF-8 files (no acces to editing BOM)

[garbanzhell]

Hi,

I am furious that this has to be so difficult.

I am using Office 2016 x64 on Windows 7

I have CSV files that can potentially have multiple language characters in them. They are encoded in UTF-8 and separated by commas.

I want to be able to double click on them to open them nicely and start working (like OpenOffice.org does).

Description

• I got around Excel not understanding commas by changing my whole Windows default separators (which is dumb, but it worked).
• I though I could get around Excel not understanding the UTF-8 by changing the registry like they explain here. However, that only changes the Default option of the dropdown called "File origin" in the Import method (Data Tab > Get External Data > From Text). While this is a useful step, it still needs me to go through the menu browsing instead of just double clicking the CSV file in the file explorer.
• I then said frack it and I resorted to create a Macro for which I would put a custom button on the ribbon. I wrote the following macro, making sure to put "Origin:=65001" BUT IT STILL DOES NOT WORK.
  
  

  Sub Open_CSV_UTF_8()
  
     filetoopen = Application.GetOpenFilename("Text Files (*.txt;*.csv), *.txt;*.csv")
     If filetoopen = Null Or filetoopen = Empty Then Exit Sub
  
     Workbooks.OpenText Filename:=filetoopen, _
     Origin:=65001, DataType:=xlDelimited, Comma:=True
  
  End Sub

  
  When I click my custom button, a dialog box appears to select the CSV file I want to open. I select it and voila it gets opened (alas, to a new file, insted of imported to the current blank workbook) but the characters are still scrambled. Obviously Workbooks.OpenText does not work.
• I noticed that maybe I could create a macro that would import the data (the the current document) by adding a connection to the file and creating a table from that connection, however,
  I do not know how to create a macro that would open a dialog box for me to select the file.

Questions

1. Is there a way to change the Registry for the opening (and not the import dialog) of CSV files?
2. If not, is there a way to make my first Workbooks.OpenText macro to work as expected (Origin:=65001)?
3. If not, could yo help figure out a VBA macro for making a connection and open up a dialog box to browse for the CSV file?

Thanks a bunch!!

Cross-posted: https://chandoo.org/forum/threads/open-csv-utf-8-files-no-acces-to-editing-bom.36165/

 

[thisoldman]

It annoys me no end that Excel does not transparently open utf-8 files and then insists on calling utf-16 Unicode.

This might work well enough for your third option without VBA. From the Excel ribbon, select Data >> Get External Data >> From Text.

I didn't have a multi-language csv to try but Marcus Kuhn's example file, from https://www.cl.cam.ac.uk/~mgk25/ucs/examples/UTF-8-demo.txt, opened and the text was not mangled. The Math symbols were out of alignment using Consolas as the font and there was only a minor glitch with the box drawing figures.

I also opened a Chinese poem, encoded as utf-8, using the same method. The poem I used was from http://www.humancomp.org/unichtm/maopoem8.htm.

Another thing to try is to copy the csv and add ".txt" as the final extension: "somefile.csv" copied to "somefile.csv.txt". Then use "Open with..." from the file Explorer right-click menu to open with Excel.

 

[garbanzhell]

THank you @thisoldman. However, the main point of my quest is to reduce clicks and menu browsing to a minimum (therefore improving times and reducing error).

 

[garbanzhell]

Also, here's a sample file: https://1drv.ms/u/s!AhY6uGmk9c52gfY7Q6yh4kkpxJi2Tw