Rectangle 27 0

excel VBA importing UTF 8 CSV file from a web server?


I have tried different Country Codes and found that in my specific scenario setting Origin:=1252 (1252 - windows-1252 - ANSI Latin 1; Western European (Windows)) loads the file in Excel just fine.

I would think that as soon as you link the file with Excel, it should try to read the header of the file and select the correct Country Code automatically (well, assuming the header is not missing).

IMO, there seems to be a bug/conflict in Excel when opening UTF-8/UTF-8-BOM files using the recorded macro code, specifically when the Origin parameter is set to 65001 which is supposed be UTF-8.

If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.

Remove the Origin parameter from the function call and see if the file loads properly Workbooks.OpenText Filename:="C:\file.csv".

Note
Rectangle 27 0

excel VBA importing UTF 8 CSV file from a web server?


I have tried different Country Codes and found that in my specific scenario setting Origin:=1252 (1252 - windows-1252 - ANSI Latin 1; Western European (Windows)) loads the file in Excel just fine.

I would think that as soon as you link the file with Excel, it should try to read the header of the file and select the correct Country Code automatically (well, assuming the header is not missing).

IMO, there seems to be a bug/conflict in Excel when opening UTF-8/UTF-8-BOM files using the recorded macro code, specifically when the Origin parameter is set to 65001 which is supposed be UTF-8.

If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.

Remove the Origin parameter from the function call and see if the file loads properly Workbooks.OpenText Filename:="C:\file.csv".

Note
Rectangle 27 0

excel VBA importing UTF 8 CSV file from a web server?


Sub DownloadAndLoad
  DownloadFile "http://myserver.com/myFile.csv","C:\myFile.csv"
  OpenCsv "C:\myFile.csv"
End Sub
Sub DownloadFile(ByVal url As String, ByVal local As String)

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", url, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile local, 2 
    oStream.Close
End If

End Sub
Sub OpenCsv(ByVal csvfile As String)
Workbooks.OpenText Filename:= _ 
csvfile,Local:=True,StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False
End Sub

Hi Uri. Thanks for your reply. In my case, the Local:=True doesn't change a thing (is it cause my locals are UK? Changing my locale isn't really an option as I will be processing files of many languages...). For example, the csv file has a in it, when I use the Workbooks.Open the character is still loaded as . I have noticed that when I do this manually (not via macro) the csv is loaded properly. I have recorded a macro from the manual steps but the macro behaviour is different. I have read that csv files may be missing UTF-8 heading - are you familiar with headings at all?

If the characters are displayed correctly when you download the csv file yourself, I'd divide the process to 2 stages:

Local should not be used or have an effect when reading UTF-8. @Meehow Yes, putting a UTF-8 BOM in the file fixes it.

Note That: The Local parameter is the key here,it makes VBA use your excel's local configuration (vietnamese), which is by default set to False.

Note
Rectangle 27 0

excel VBA importing UTF 8 CSV file from a web server?


Sub DownloadAndLoad
  DownloadFile "http://myserver.com/myFile.csv","C:\myFile.csv"
  OpenCsv "C:\myFile.csv"
End Sub
Sub DownloadFile(ByVal url As String, ByVal local As String)

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", url, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile local, 2 
    oStream.Close
End If

End Sub
Sub OpenCsv(ByVal csvfile As String)
Workbooks.OpenText Filename:= _ 
csvfile,Local:=True,StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False
End Sub

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Hi Uri. Thanks for your reply. In my case, the Local:=True doesn't change a thing (is it cause my locals are UK? Changing my locale isn't really an option as I will be processing files of many languages...). For example, the csv file has a in it, when I use the Workbooks.Open the character is still loaded as . I have noticed that when I do this manually (not via macro) the csv is loaded properly. I have recorded a macro from the manual steps but the macro behaviour is different. I have read that csv files may be missing UTF-8 heading - are you familiar with headings at all?

If the characters are displayed correctly when you download the csv file yourself, I'd divide the process to 2 stages:

Local should not be used or have an effect when reading UTF-8. @Meehow Yes, putting a UTF-8 BOM in the file fixes it.

Note That: The Local parameter is the key here,it makes VBA use your excel's local configuration (vietnamese), which is by default set to False.

Note
Rectangle 27 0

excel VBA importing UTF 8 CSV file from a web server?


Function ReadUTF8CSVToSheet(file As String)
    Dim ws As Worksheet
    Dim strText As String

    ' read utf-8 file to strText variable
   With CreateObject("ADODB.Stream")
        .Open
        .Type = 1  ' Private Const adTypeBinary = 1
        .LoadFromFile file
        .Type = 2  ' Private Const adTypeText = 2
        .Charset = "utf-8"
        strText = .ReadText(-1)  ' Private Const adReadAll = -1
    End With

    ' parse strText data to a sheet
    Set ws = Sheets.Add()
    intRow = 1
    For Each strLine In Split(strText, chr(10))
        If strLine <> "" Then
            With ws
                .Cells(intRow, 1) = strLine
                .Cells(intRow, 1).TextToColumns Destination:=Cells(intRow, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=True, Space:=False, Other:=False
            End With

            intRow = intRow + 1
        End If
    Next strLine

    ReadUTF8CSVToSheet = ws.Name

End Function

' to run
strSheetName = ReadUTF8CSVToSheet("C:\temp\utf8file.csv")

I have been looking at a similar problem where we import utf-8 encoded csv files in to a worksheet. I am not pulling the data from a web server but this might help.

My solution is to read the utf-8 file to a local variable then insert it into a sheet. I tried saving the data to a temp file with ansi encoding but doing this caused all the characters to lose their accents.

Note
Rectangle 27 0

excel VBA importing UTF 8 CSV file from a web server?


Function ReadUTF8CSVToSheet(file As String)
    Dim ws As Worksheet
    Dim strText As String

    ' read utf-8 file to strText variable
   With CreateObject("ADODB.Stream")
        .Open
        .Type = 1  ' Private Const adTypeBinary = 1
        .LoadFromFile file
        .Type = 2  ' Private Const adTypeText = 2
        .Charset = "utf-8"
        strText = .ReadText(-1)  ' Private Const adReadAll = -1
    End With

    ' parse strText data to a sheet
    Set ws = Sheets.Add()
    intRow = 1
    For Each strLine In Split(strText, chr(10))
        If strLine <> "" Then
            With ws
                .Cells(intRow, 1) = strLine
                .Cells(intRow, 1).TextToColumns Destination:=Cells(intRow, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=True, Space:=False, Other:=False
            End With

            intRow = intRow + 1
        End If
    Next strLine

    ReadUTF8CSVToSheet = ws.Name

End Function

' to run
strSheetName = ReadUTF8CSVToSheet("C:\temp\utf8file.csv")

I have been looking at a similar problem where we import utf-8 encoded csv files in to a worksheet. I am not pulling the data from a web server but this might help.

My solution is to read the utf-8 file to a local variable then insert it into a sheet. I tried saving the data to a temp file with ansi encoding but doing this caused all the characters to lose their accents.

Note