I've got an external device that Emails me .csv files with UTF-8 encoding.
The file contains numbers which may have leading zeroes and strings which may contain special characters.
Reading them seems to be problematic. What I get depends on how I read them.
For example, with Notepad or Word I get "Don’t change me!", if I read it as a TextStream with TextFileRead, I get "Don’t change me!", If I read it into a new sheet with OpenText, I get "Donât change me!"
The first line of the file defines the separator. This makes it difficult to use OpenText without opening the file twice. In any case, OpenText seems to have its own problems.
Code: Select all
Sub getData()
Dim vColumns(1 To 7) As Variant
Dim iOrigin As Integer
Dim iIndex As Integer
iOrigin = 65001 ' MS codepage for UTF-8
For iIndex = 1 To 7
vColumns(iIndex) = Array(iIndex, xlTextFormat)
Next iIndex
Workbooks.OpenText Filename:= _
".\TestCases\TC7c1.csv", _
Origin:=65001, DataType:=xlDelimited, Semicolon:=True, _
TextVisualLayout:=vColumns
End Sub ' getData
The created worksheet does not use the TextVisualLayout info as documented at http://msdn.microsoft.com/en-us/library ... 11%29.aspx" onclick="window.open(this.href);return false;. The first line in the file is ignored. Dates and times are converted, numbers lose leading zeroes, etc.
Short of building my own UTF-8 datastream decoder, is there a quick fix?
TIA for any insight
ZK