• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

G

garbanzhell

New Member
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 explainhere. 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 saidfrackit 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.

    Code: 
    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!!

Originally posted at https://www.mrexcel.com/forum/excel...8-files-no-acces-editing-bom.html#post4934747
 
Hmm, in this case instead of using ".OpenText" I'd use ".QueryTables.Add" method.

Sample code...
Code: 
Sub Sample()
Dim cn As WorkbookConnection
filetoopen = Application.GetOpenFilename("Text Files (*.txt;*.csv), *.txt;*.csv")
If filetoopen = Null Or filetoopen = Empty Then Exit Sub

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & filetoopen, Destination:=Range("$A$1" _
        ))
        .Name = "sample"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    For Each cn In ActiveWorkbook.Connections
        cn.Delete
    Next
End Sub

Adjust setting as needed.
 
Chihiro said:
Hmm, in this case instead of using ".OpenText" I'd use ".QueryTables.Add" method.

Sample code...
Code: 
Sub Sample()
Dim cn As WorkbookConnection
filetoopen = Application.GetOpenFilename("Text Files (*.txt;*.csv), *.txt;*.csv")
If filetoopen = Null Or filetoopen = Empty Then Exit Sub

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & filetoopen, Destination:=Range("$A$1" _
        ))
        .Name = "sample"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    For Each cn In ActiveWorkbook.Connections
        cn.Delete
    Next
End Sub

Adjust setting as needed.
Click to expand...
Thank you @Chihiro . This is actually quite useful, it can solve my problem number 2 and 3. Still, it leaves open the question of whether this could be accomplished by just double clicking on the file.

AlanSidman said:
crossposted: https://www.ozgrid.com/forum/forum/...-open-csv-utf-8-files-no-acces-to-editing-bom
Click to expand...
Yes. I cross-posted everywhere. I am deleting all posts and restricting discussion to just one forum: https://www.mrexcel.com/forum/excel-questions/1028187-open-csv-utf-8-files-no-acces-editing-bom.html
 
You must log in or register to reply here.
Back
Top