CODEWITHSUNDEEP
excelspecial-charactersvba
zicon117
zicon117

Reputation: 53

Special Characters from txt file to excel

I am trying to import special characters from a txt file into excel. I've tried so many things but the characters BREAK in excel. example of my string:

in txt: Changjíhuízúzìzhìzhou converts in excel to: Changjíhuízúzìzhìzhou

so I tried moving values over bit by bit but no luck..

Sub ImportTXTFile()

Dim file As Variant
Dim EXT As String
Dim Direct As String    ' directory...
Direct = "C:\FilePath\Here\"
EXT = ".txt"
Dim COL As Long
Dim row As Long
COL = 1
row = 1

file = Dir(Direct)

Do While (file <> "")   ' Cycle through files until no more files
    If InStr(file, "Data.txt") > 0 Then 
        '
        Open Direct & "Data.txt" For Input As #1               
        '
        While Not EOF(1)
            Line Input #1, DataLine ' Read in line
            Do While DataLine <> ""
                If InStr(DataLine, ",") = 0 Then    ' Drop value into excel upto the first ,
                    Sheets("test").Cells(row, COL).Value = DataLine
                    DataLine = ""
                Else
                    Sheets("test").Cells(row, COL).Value = Left(DataLine, InStr(DataLine, ",") - 1)
                    DataLine = Right(DataLine, Len(DataLine) - InStr(DataLine, ","))    ' rebuild array without data upto first ,
                End If
                COL = COL + 1   ' next column
            Loop
            COL = 1             ' reset column
            row = row + 1       ' write to next row
        Wend
        '
        Close #1    ' Close files straight away
    End If
    file = Dir
Loop
MsgBox "Data Updated"
End Sub

Upvotes: 3

Views: 5646

Answers (2)

danieltakeshi
danieltakeshi

Reputation: 939

Using the first link i gave you, here is a test code, i tested with success. Using the charset: CdoISO_8859_1

Dim objStream As Object
Dim strData As String
Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "iso-8859-1"
objStream.Open
objStream.LoadFromFile ("C:\Users\user_name\Desktop\test.txt")
strData = objStream.ReadText()
Debug.Print strData & " Compare to: Changjíhuízúzìzhìzhou"

The output was: immediate

EDIT:

Check the encoding type of your .txt file and import to Excel with the same encoding charset, for example, i changed the test.txt to UTF-8 and imported successfully with the .Charset as "utf-8"

You can Save As your .txt file and choose the encoding.

.txt save as

Upvotes: 1

zicon117
zicon117

Reputation: 53

So I want to cry because all this converting of UTF-8 to ASCII can be avoid simply by: opening the txt file in Notepad++ going to the encoding tab clicking convert to ASCII

ran my original code. BLAM everything is perfect.

Thank you danieltakeshi for all your help!

Upvotes: 2

Related Questions