Read special character from text file

[YasserKhalil]

Hello everyone
I have a text file and this is part of the text in the text file

Code: Select all

:↵:

When trying to read the contents of the text file using this line

Code: Select all

CreateObject("Scripting.FileSystemObject").OpenTextFile(ThisWorkbook.Path & "\FormData.txt").ReadAll

This special character reads in incorrect way
How can I read it correctly ??

[HansV]

Do you have a sample text file for us?

[YasserKhalil]

Here's the file

[HansV]

Thanks. Scripting.FileSystemObject always uses ANSI, it doesn't support Unicode.
You might use ADODB. Its Stream object lets you specify the encoding:

Code: Select all

Sub Test()
    Dim stm As Object
    Dim lin As String
    Dim pos As Long
    Dim c As String
    Set stm = CreateObject("ADODB.Stream")
    With stm
        .Open
        .Type = 2 ' adTypeText
        .Charset = "UTF-8"
        .LoadFromFile ThisWorkbook.Path & "\FormData.txt"
        lin = .ReadText
        .Close
        pos = InStr(lin, ":")
        c = Mid(lin, pos + 1, 1)
        Debug.Print AscW(c)
    End With
End Sub

[YasserKhalil]

That's what I want exactly. Thank you very much.

[Doc.AElstein]

Hi
Just out of interest, what should be the correct answer?
Han's macro gives me 8629
I tried a couple of ways and got 226 and 63

Alan

[YasserKhalil]

The correct answer for me is 8629

[Doc.AElstein]

Thx

[HansV]

If you use the Asc function, you get 63 - this indicates that the character is actually a multi-byte Unicode character.

[Doc.AElstein]

Hi Hans
I used AscW in both cases.

I was curious to see what the normal VBA import with the “OpenForAsWay” did since I use that a lot to try and analyse what is in a text file, or text string.

I downloaded Yasser’s file by whatever is the default way on my computer ( I shortened the text file a bit to just include that strange character) and then I used this macro

Code: Select all

 Sub ImportViaVBAOpenForAsWay()
' get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & "\" & "FormDataShortened.txt"   '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' see whats in it
Dim Pos As Long, strC As String
 Let Pos = InStr(TotalFile, ":")
 Let strC = Mid(TotalFile, Pos + 1, 1)
 Debug.Print AscW(strC)
End Sub

That returned me 226

I opened the text file and changed the default save option to ANSI ( it was set at UTF-8 by default )

The macro then returned 63

I don’t really understand all this different text format stuff, but recently was trying to understand text file formats a bit better, that’s all


Alan