PDA

View Full Version : [SOLVED] Open CSV UTF-8 files (no acces to editing BOM)



garbanzhell
10-23-2017, 03:21 AM
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 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 . 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


Is there a way to change the Registry for the opening (and not the import dialog) of CSV files?
If not, is there a way to make my first Workbooks.OpenText macro to work as expected (Origin:=65001)?
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!!

ETA: question originally posted at mrexcel.com

Jan Karel Pieterse
10-23-2017, 04:50 AM
As I also posted at microsoft techcommunity: check out http://jkp-ads.com/articles/importtext.asp

garbanzhell
10-23-2017, 11:48 AM
Cross posting is wrong and I repent.

mancubus
10-24-2017, 04:29 AM
Cross posting is wrong
no, cross posting is not wrong as long as you provide the links to the threads in the other forums / communities.