none
Workbooks.OpenText does not import a utf-8 csv in maori - but same csv imports fine manually in excel RRS feed

  • Question

  • Hi,

    I have a CSV that contains utf-8 characters. My test data has Maori characters in it.

    I can import the CSV into excel just fine via Data (ribbon) -> From Text and select origin = 65001 etc

    If I do this in VBA with the following code is mangles the Maori macrons:

    <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica} </style>

    Workbooks.OpenText Filename:=CSVFileName, Origin:=65001, DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, Comma:=True

    Sample text from manual import = Testing Māori (notice how the macron above the a is fine)

    Sample text from vba import on same CSV = Testing Māori (close but not correct)

    All tips gratefully received! Thanks,

    Wednesday, October 3, 2018 2:07 AM

Answers

  • Hi Tiberius1701a,

    Assuming you run the code in the same workbook you need to collect CSV data to, please try the following code:

    Public Sub Example()
        Const csPath As String = "starting_positions.csv"
        Dim ws As Excel.Worksheet
        Set ws = ThisWorkbook.Sheets(1)
        With ws.UsedRange.ClearContents
    	.QueryTables.Add("TEXT;" & csPath, ws.Cells(1, 1))
            .FieldNames = True
            .AdjustColumnWidth = True
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileCommaDelimiter = True
            ''// This array will need as many entries as there will be columns:
            .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat)
            .Refresh
        End With
    End Sub

    One more hint: in case you're going to use this macro many times - do not create every time a new connection. In case all the options are similar - only use .Refresh method.

    If you're stuck with the merging of code parts - try to use macro recorder passing any of the steps manually. 

    For more information, please refer to the link as below:

    Reading CSV file into excel using VBA then outputting the results to a specific worksheet

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by Tiberius1701a Friday, October 5, 2018 6:23 AM
    Wednesday, October 3, 2018 6:41 AM

All replies

  • Hi Tiberius1701a,

    Assuming you run the code in the same workbook you need to collect CSV data to, please try the following code:

    Public Sub Example()
        Const csPath As String = "starting_positions.csv"
        Dim ws As Excel.Worksheet
        Set ws = ThisWorkbook.Sheets(1)
        With ws.UsedRange.ClearContents
    	.QueryTables.Add("TEXT;" & csPath, ws.Cells(1, 1))
            .FieldNames = True
            .AdjustColumnWidth = True
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileCommaDelimiter = True
            ''// This array will need as many entries as there will be columns:
            .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat)
            .Refresh
        End With
    End Sub

    One more hint: in case you're going to use this macro many times - do not create every time a new connection. In case all the options are similar - only use .Refresh method.

    If you're stuck with the merging of code parts - try to use macro recorder passing any of the steps manually. 

    For more information, please refer to the link as below:

    Reading CSV file into excel using VBA then outputting the results to a specific worksheet

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by Tiberius1701a Friday, October 5, 2018 6:23 AM
    Wednesday, October 3, 2018 6:41 AM
  • Hi Tiberius1701a,

    Thanks for your asking. Please remember to mark the replies(Include your solution) as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

    Best Regards,

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.

    Friday, October 5, 2018 6:17 AM
  • Hi Lina - thanks very much indeed!

    Fantastic solution. I also just found out about adding a BOM to the CSV and that helped too.

    Cheers :)

    Friday, October 5, 2018 6:23 AM