Reading basic .txt file fails, how can it be?

TheBigBoss · #1 09-14-2022, 08:35 AM

Hi there,

Running a macro in Words that parse a .txt file and then deal with MS Word properties, bookmarks and raw content inserted in table.

I did a test with a .txt file I typed in, it works. I have been using .txt file for a year on One Drive . No issue. So today, I completed a template request online which allow users to send request to generate docx.

When I run the .txt file I received by email, code fails after reading the first line, there is a "0" hanging. How I can it be? There must something in that txt file sent by email (PHP generated) that I can't see! Throwing a "0".

Thanks

Guessed · #2 09-14-2022, 03:42 PM

What is the point of your post? If you can't see it and don't show it to us then how are we going to see it for you?

Your text file doesn't resemble the outputs in your Immediate window so you've posted a random text file and expect us to work out a character from another file which you didn't give us.

Your code opens Word only briefly to read a text file into a string - this seems like overkill? What program is the VBA code running in and why don't you just open it without needing Word. If you go to the effort of creating a Word instance then you should use it to convert the text content into a Table and just read that column of text.

Guessed · #3 09-14-2022, 03:55 PM

Ahh, I see what your problem is. The Split command is not returning a two dimensional array. Have a look at how many 'lines' you have after running the split command.

If you split a string containing carriage returns but don't use those CRs as the split trigger then the result will include those CRs in the middle of the array item. This means that
" ++ 105<CR>0 ++ " splits to become an item in the array which has a value of "105<CR>0"

I think you will find that this line is not doing what you expect it is
Lines = Split(Textline, vbNewLine)

If it was, would you see the value of lines(i) being as large as 20 paragraphs?

TheBigBoss · #4 09-14-2022, 09:53 PM

Quote:

Originally Posted by Guessed

What is the point of your post? If you can't see it and don't show it to us then how are we going to see it for you?

Your text file doesn't resemble the outputs in your Immediate window so you've posted a random text file and expect us to work out a character from another file which you didn't give us.

Your code opens Word only briefly to read a text file into a string - this seems like overkill? What program is the VBA code running in and why don't you just open it without needing Word. If you go to the effort of creating a Word instance then you should use it to convert the text content into a Table and just read that column of text.

Hi Guessed, thanks for your reply.
Yes, the txt file doesn't resemble the outputs as I deleted most of its content to only keep the first lines. But for sure, this is the file being problematic.

The VBA is ran on MS Word and there is much more to it, parsing the .txt file and creating an array for each line is required. Also, txt files contain content but also data, properties, bookmarks value and other stuff... It read the properties first then apply properties, then further macros are run to create content (including header/footer, table, etc) depending of property value, then content is further inserted. Agree, it could have been otherwise, like doing without txt file and putting all data in .docx properties.

Sorry if my question wasn't clear. This said, there is something in that txt file that throws a return followed by a "zero"....

TheBigBoss · #5 09-14-2022, 10:17 PM

Quote:

Originally Posted by Guessed

Ahh, I see what your problem is. The Split command is not returning a two dimensional array. Have a look at how many 'lines' you have after running the split command.

If you split a string containing carriage returns but don't use those CRs as the split trigger then the result will include those CRs in the middle of the array item. This means that
++ 105<CR>0 ++ splits to become and item in the array which is 105<CR>0

Guessed, thanks for helping. Your input is very helpful... indeed, issue is clearly the line break character.

I generate the .txt file with PHP and I use PHP_EOL for line break, which adds \r, \n\, or both \r\n, depending of server (Windows or Linux) - I need to check this. Also, it seems that opening files in txt mode in Windows will also change CRLF into CR...

Well, I need to do more testing.
a) Use something else than PHP_EOL for line break (in PHP)
b) Clean the line in VBA before splitting, to remove any vbLf, vbCr, vbCrLf and/or vbNewLine.

Thanks a lot

I wish there was a way to avoid this, generating

TheBigBoss · #6 09-15-2022, 02:33 AM

Hi Guessed,

Splitting the lines with vbLf fixed it. No more <CR>, no more hanging "0".

Code:

lines = Split(Input$(LOF(hf), #hf), vbLf)

And you were totally right (sorry I misunderstood you here) but creating a MS Word instance for parsing the txt file was way too much (and may be the reason for the problem).... so I used Open to deal directly with .txt file

Code:

Sub DCS_Get_Properties(sFN As String)
    ' Load core content using data txt file
    Dim hf As Integer: hf = FreeFile
    Dim oD As Object, PropLang As String, PropCat As String, PropNoSymbol As String, _
        bool As Boolean, sFE As String, dc(60, 8) As String, _
        i As Integer, lines() As String, tmp2() As String
    Open sFN For Input As #hf
    lines = Split(Input$(LOF(hf), #hf), vbLf)
    Close #hf
    For i = 0 To UBound(lines)
        If (Trim(lines(i))) <> "" Then
            Debug.Print "line " & lines(i)
            tmp2 = Split(lines(i), " ++ ")
            dc(i, 0) = tmp2(0) ' Table no
            dc(i, 1) = tmp2(1) ' Row no
            dc(i, 2) = tmp2(2) ' Cell no
            dc(i, 3) = tmp2(3) ' Data Type
            dc(i, 4) = tmp2(4) ' Content type
            dc(i, 5) = tmp2(5) ' Cell style
            dc(i, 6) = tmp2(6) ' Cell content
        End If
    Next
    For i = 0 To UBound(lines)
        Debug.Print "CONTENT - " & dc(i, 6)
        If dc(i, 3) = "Prop" Then
            If dc(i, 4) = "PropLang" Then
                PropLang = dc(i, 6)
                Call WriteProp("Prop-Language", PropLang)
            ElseIf dc(i, 4) = "PropCat" Then
                PropCat = dc(i, 6)
                Call WriteProp("Prop-Category", PropCat)
            ElseIf dc(i, 4) = "PropNoSymbol" Then
                PropNoSymbol = dc(i, 6)
                Call WriteProp("Prop-NoSymbol", PropNoSymbol)
            Else
            End If
        Else
        End If
    Next

I wish to thank you for your reply, it did help.

TheBigBoss · #7 09-15-2022, 05:37 AM

All right, here is the function and attached is the txt file with six (6) entries.

So it worked doing a vbLf Split of the text file, followed by a replace of vbCr with vbNullString.
All other combinations failed, I either get that hanging 0 and/or return.
Really strange??

I will mark this post resolved still in a few days

Code:

Sub TEST_Get_Properties()
    Dim hf As Integer: hf = FreeFile
    Dim oD As Object, PropLang As String, PropCat As String, PropNoSymbol As String, _
        ReqEmail As String, ReqID As String, sFE As String, dc(60, 8) As String, _
        i As Integer, lines() As String, tmp2() As String
    sFN = "C:\Users\" & Environ("UserName") & _
            "\615e8498df6dc933e3baf8732fe5ecf38f4a62b5.txt"
    ' Load core content using data txt file
    Open sFN For Input As #hf
    'lines = Split(Input$(LOF(hf), #hf), vbCr)
    'lines = Split(Input$(LOF(hf), #hf), vbCrLf)
    lines = Split(Input$(LOF(hf), #hf), vbLf)
    Close #hf
    For i = 0 To UBound(lines)
        If (Trim(lines(i))) <> "" Then
            ' Needed, to fix a bug
            ' lines(i) = Replace(lines(i), vbCrLf, vbNullString)
            ' lines(i) = Replace(lines(i), vbNewLine, vbNullString)
            ' lines(i) = Replace(lines(i), vbLf, vbNullString)
            lines(i) = Replace(lines(i), vbCr, vbNullString)
            Debug.Print "LINE" & i + 1 & ": " & lines(i) ' Show lines
            tmp2 = Split(lines(i), " ++ ")
            dc(i, 0) = tmp2(0) ' Table no
            dc(i, 1) = tmp2(1) ' Row no
            dc(i, 2) = tmp2(2) ' Cell no
            dc(i, 3) = tmp2(3) ' Data Type
            dc(i, 4) = tmp2(4) ' Content type
            dc(i, 5) = tmp2(5) ' Cell style
            dc(i, 6) = tmp2(6) ' Cell content
        End If
    Next
    ' Write properties
    For i = 0 To UBound(lines)
        Debug.Print "CONTENT" & i + 1 & ": " & dc(i, 6)
        If dc(i, 3) = "Prop" Then
            If dc(i, 4) = "PropLang" Then
                PropLang = dc(i, 6)
                Call WriteProp("Prop-Language", PropLang)
            ElseIf dc(i, 4) = "PropCat" Then
                PropCat = dc(i, 6)
                Call WriteProp("Prop-Category", PropCat)
            ElseIf dc(i, 4) = "PropNoSymbol" Then
                PropNoSymbol = dc(i, 6)
                Call WriteProp("Prop-NoSymbol", PropNoSymbol)
            Else
            End If
        ElseIf dc(i, 3) = "Request" Then
            If dc(i, 4) = "Email" Then
                'PropCat = dc(i, 6)
                Call WriteProp("Prop-ReqEmail", dc(i, 6))
             ElseIf dc(i, 4) = "ID" Then
                'PropCat = dc(i, 6)
                Call WriteProp("Prop-ReqID", dc(i, 6))
            Else
            End If
        Else
        
        End If
    Next
    Debug.Print "Properties set."
    Exit Sub
End Sub

Guessed · #8 09-15-2022, 04:28 PM

Here is a concept to read the text into an array of arrays.
Source: Function was based on the stellar work of Bob77 on StackOverflow - vb6 - Array and Split commands to create a 2 dimensional array - Stack Overflow

Code:

Sub TEST_Get_Properties()
  Dim hf As Integer, sFN As String, lines As String
  Dim myArray As Variant, iRow As Integer, iCol As Integer
  sFN = "C:\Users\" & Environ("UserName") & "\615e8498df6dc933e3baf8732fe5ecf38f4a62b5.txt"
  ' Load core content using data txt file
  hf = FreeFile
  Open sFN For Input As #hf
    lines = Input$(LOF(hf), #hf)
  Close #hf
  myArray = SplitSplit(lines)
  For iRow = 0 To UBound(myArray)
    For iCol = 0 To UBound(myArray(iRow))
      Debug.Print "Row:" & iRow, "Col:" & iCol, myArray(iRow)(iCol)
    Next
  Next
End Sub

Private Function SplitSplit(ByRef Delimited As String, Optional sDiv1 As String = vbLf, Optional sDiv2 As String = " ++ ") As Variant
  Dim Rows() As String, AryOfArys As Variant, I As Long
  Rows = Split(Delimited, sDiv1)
  ReDim AryOfArys(UBound(Rows))
  For I = 0 To UBound(Rows)
    AryOfArys(I) = Split(Rows(I), sDiv2)
  Next
  SplitSplit = AryOfArys
End Function

TheBigBoss · #9 09-19-2022, 01:49 AM

I now recall why I run .txt file in a new Word.application
My .txt files have special characters, including non western (Arabic, Chinese) and I got issues reading .txt files

I tried to implement the method here - Solving the Unicode, UTF8, UTF16 and Text Files conundrum in VBA - Francesco Foti's weblog - which read text file as binary and convert special characters but I failed to even run it. Too bad as the code works for all platform (Windows, MAC). Other common method I have found is ADOB.stream with Charset = "utf-8".

Txt files seem to be problematic, I am wondering if I am not better consider using .docx instead.

Guessed · #10 09-19-2022, 04:04 AM

Quote:

I generate the .txt file with PHP and I use PHP_EOL for line break, which adds \r, \n\, or both \r\n, depending of server (Windows or Linux)

If you have control over the creation of the text file, can you not ensure that the created text file DOESN'T contain things that you can't deal with at the other end. Can you specify the PHP-created file format so the processing doesn't need to jump through hoops to decode it.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
The “spelling and grammar” check fails to work as the number of words in the file exceeds a certain	Jamal NUMAN	Word	3	09-24-2019 10:45 AM
Need plugin for reading+converting .pdf file into .doc	pstein	Word	1	05-24-2015 07:05 AM
Reading pane on TOP?	hehunt	Outlook	0	04-10-2014 06:34 AM
How to save a PPT file in 'Reading View' to be opened always in 'Reading View'	ItzVickey	PowerPoint	2	08-08-2012 09:23 AM
Word Basic 2003 to Word Basic 2007 Incompatibility	Mark1110	Word VBA	1	12-29-2010 11:04 PM