Extract text from a text file with Chinese characters using vba

Question

I have a batch of like 100,000 text files which I would like to extract as strings using vba. In the past I have been doing so this way without problem:

Sub Main()
Dim PathAndName As String
Dim TextFile As Integer
Dim TextString() As String
Redim TextString(100000)
For i = 1 To 100,000
    PathAndName = "C:\File_" & i & ".ext"
    TextFile = 1
    Open PathAndName For Input As TextFile
    TextString(i) = Input(LOF(TextFile), TextFile)
Next i
End Sub

This time, the script returns the error "Input Past End of File" Error 62. The only different I can spot is that this time the text files contain a few Chinese Characters, which are not of my interest actually. That's why I believe this is the source of the problem. The Chinese Characters appear at the first line of the files.

Any help is appreciated. Thanks!

Sorry for missing out. The Chinese Characters are also present on the 3rd last line. — Matthew Hui
– Matthew Hui, Commented Jan 23, 2016 at 9:11
PathAndName = "C:\File_" & i & ".ext" should be PathAndName = "C:\File_" & i & ".txt". and add Close TextFilebefore Next i — iJay
– iJay, Commented Jan 23, 2016 at 9:59

Axel Richter · Accepted Answer · 2016-01-23 10:42:14Z

I suspect your text file is in a multibyte encoding now. There one character is encoded in two or three bytes. So LOF(TextFile) will not return the correct character count but the byte count. But Input(LOF(TextFile), TextFile) needs the character count since it must create a String.

You could use:

Sub Main()
Dim PathAndName As String
Dim TextFile As Integer
Dim TextString() As String
Redim TextString(100000)
For i = 1 To 100000
    PathAndName = "C:\File_" & i & ".ext"
    TextFile = 1
    Open PathAndName For Input As TextFile

    Dim sLine As String
    Dim sTextString As String
    sLine = ""
    sTextString = ""

    Do While Not EOF(TextFile)
     Input #TextFile, sLine
     sTextString = sTextString & sLine
    Loop

    TextString(i) = sTextString

    Close #TextFile

Next i
End Sub

But the better approach would be using ADODB.Stream instead of the dinosaur VB file access methods. But this is a totally different approach. So you should read about ADODB.Stream yourself first.

Collectives™ on Stack Overflow

Extract text from a text file with Chinese characters using vba

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related