XHR request response text has unexpected character set

Question

I was looking at the answer, by @OmegaStripes, to this question How to get a particular InnerText from a specific class? Here one uses the Split function, and a specified delimiter string, to extract an href from .responseBody.

I then tried to replicate this to extract the following href :

"https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/02/New-AmbSYS-to-2018-Jan.csv"

from NHS England's Ambulance Quality Indicators

HTML snippet:

<main class="main group" role="main">
        <div class="page-content" id="main-content">
            <header>
                <h1>Ambulance Quality Indicators</h1>
            </header>
            <article class="rich-text">
               <p></p>
              <p></p>
              <p></p>
               <p></p>
              <p></p>
              <p><strong>CSV Data</strong><br>
These files have the same data as other published spreadsheets, but without any formatting:<br>
                <a href="https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/02/New-AmbSYS-to-2018-Jan.csv" class="csv-link" onclick="ga('send', 'event', 'Downloads', 'CSV', 'https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/02/New-AmbSYS-to-2018-Jan.csv');">New Systems Indicators August 2017 to January 2018 (CSV, 23KB)</a><br>
            </article>
    </div>
</main>

Problem:

I am getting response text back that looks as follows:

Example response text:

From a quick bit of research, see references, I am guessing this is perhaps an encoding problem?

I tried setting a .SetRequestHeader

 .setRequestHeader "Content-Type", _
     "application/x-www-form-urlencoded; charset=UTF-8"

This made no difference to the output.

To be honest, I haven't a clue how to resolve this.

Any suggestions please on how I get the expected response text? i.e. that I can parse for the href of interest.

Context:

This is part of a bigger piece of work where:

1) I want to scrape that CSV link (the name of which will change each month), without having the browser pop-up

2) Download the target file content

3) Use ADODB.Stream to write the binary file out.

This process was outlined by @OmegaStripes in response to my question Return focus to ThisWorkbook.Activesheet after XMLHTTP60 file download . I am trying to understand and implement that suggestion currently.

Code:

Option Explicit

Public Const url As String = "https://www.england.nhs.uk/statistics/statistical-work-areas/ambulance-quality-indicators/"
Public aBody As String

Sub Testing()

    ' Download via XHR
    With CreateObject("MSXML2.XMLHTTP")

        .Open "GET", url, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=utf-8"
        .send
        ' Get binary response content
        aBody = .responseBody

    End With

    ActiveSheet.Range("A1") = aBody

End Sub

References:

1) XMLHTTP and Special Characters (eg, accents)

2) setRequestHeader Method (IXMLHTTPRequest)

3) VBA HTML Scraping - '.innertext' from complex table

4) Msxml2.ServerXMLHTTP and UTF-8 charset issues

The response headers don't specify the encoding, which is probably why `MSXML2.XMLHTTP` is not properly decoding the body. Use an`ADODB.Stream` with `stream.CharSet = "UTF-8"`. For an example: https://stackoverflow.com/questions/26624736/convert-binary-to-string/26625611#26625611 — Florent B., Feb 12 '18 at 14:50
@FlorentB. Thanks. I will have a look. I don't know if my ignorance but this seems to be for a later stage. Would this somehow link with the response text? I first have to identify the file url from the XHR. — QHarr, Feb 12 '18 at 15:08
be aware that `.responseBody` returns an array of bytes encoded as UTF-8. You are casting it to a `String` (`UTF-16` encoding) which is why you are getting all these foreign characters. If the CSV file contains only ASCII characters, then use `.responseText`, and if not, use `ADODB.Stream` to convert the `.responseBody`. — Florent B., Feb 12 '18 at 15:56

QHarr · Accepted Answer · 2018-02-13T08:55:19.067

So credit goes to @FlorentB for this solution and a shout out to @OmegaStripes for the suggestion.

As suggested, the problem indeed was the .responseBody was returning an array of bytes encoded as UTF-8. As pointed out, I was casting it to a String (UTF-16 encoding) hence all these foreign characters.

I used @Tomalak's function BytesToString, with minor changes, to handle the conversion to string.

Code:

Option Explicit

Public Const url As String = "https://www.england.nhs.uk/statistics/statistical-work-areas/ambulance-quality-indicators/"
Public aBody As String 'this is causing the conversion
Const adTypeBinary As Byte = 1
Const adTypeText As Byte = 2
Const adModeReadWrite As Byte = 3
Public Const strPath As String = "C:\Users\User\Desktop\testXMLHTTPOutput"

Public Sub Testing() 
    ' Download via XHR
    With CreateObject("MSXML2.XMLHTTP")

        .Open "GET", url, False
        .send
        ' Get binary response content
        aBody = BytesToString(.responseBody, "UTF-8")

    End With

    Dim fso As Object  'late binding
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = fso.CreateTextFile(strPath)
    oFile.WriteLine aBody
    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing

End Sub
'ADODB.Stream with stream.CharSet = "UTF-8"
'http://msdn.microsoft.com/en-us/library/windows/desktop/ms675032%28v=vs.85%29.aspx


Public Function BytesToString(ByVal bytes As Variant, ByVal charset As String) As String

    With CreateObject("ADODB.Stream")
        .Mode = adModeReadWrite
        .Type = adTypeBinary
        .Open
        .Write bytes
        .Position = 0
        .Type = adTypeText
        .charset = charset
        BytesToString = .ReadText
    End With
End Function

Useful other link here:

Save text file UTF-8 encoded with VBA

XHR request response text has unexpected character set

1 Answers1

Linked