VBA importing UTF-8 CSV file from a web server
If the characters are displayed correctly when you download the csv
file yourself, I'd divide the process to 2 stages:
Downloading
Sub DownloadFile(ByVal url As String, ByVal local As String)Dim WinHttpReq As ObjectSet WinHttpReq = CreateObject("Microsoft.XMLHTTP")WinHttpReq.Open "GET", url, False, "username", "password"WinHttpReq.sendmyURL = WinHttpReq.responseBodyIf WinHttpReq.Status = 200 Then Set oStream = CreateObject("ADODB.Stream") oStream.Open oStream.Type = 1 oStream.Write WinHttpReq.responseBody oStream.SaveToFile local, 2 oStream.CloseEnd IfEnd Sub
Loading CSV
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:=FalseEnd Sub
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
.
Putting it all together
Sub DownloadAndLoad DownloadFile "http://myserver.com/myFile.csv","C:\myFile.csv" OpenCsv "C:\myFile.csv"End Sub
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.
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.NameEnd Function' to runstrSheetName = ReadUTF8CSVToSheet("C:\temp\utf8file.csv")
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.
I have found two workarounds to this issue:
Remove the
Origin
parameter from the function call and see if the file loads properlyWorkbooks.OpenText Filename:="C:\file.csv"
.If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.
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).
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.