Excel VBA - Trouble importing WhatsApp chat history files into an Excel sheet

Question

Here is what a typical WhatsApp history chat file (.txt) looks like if opened in Notepad.

Sadly, it doesn't seem to be functioning well for me. I'm attempting to add the following dummy code to that already-existing file. Currently, it has two drawbacks: first, it overwrites the data rather than appending it. Second, even after the software has started, when I try to open the file, it will only let me do so in read-only mode. I can confirm that I'm also utilizing Pandas 1.4.

I've been using the Workbook so far, but I've been trying and failing badly. OpenText approach. The issue is that instead of being considered as a single message, the many lines of the to-buy list wind up in different rows.

I also need a simple and quick solution because I'll be processing enormous chat files with hundreds of messages. Of course, I could cycle through the lines and merge them depending on whether they had time, date, or username stamps, but that would take a very long time for a large file.

Answer 1

Since the OpenText method isn't working for you, let's try something similar to this, which reads files using the built-in I/O methods (Open and Line Input). It should be quicker than FileSystemObject, and since you're working with raw text/data, you'll have more flexibility than just using Workbooks. OpenText.

We may need to add some conditional logic to detect when each "line" starts if your text file is jumbled (as it appears to be in the screenshots you've provided), but let's start by seeing how this works first.

Each line shall be written progressively from row 1 in Column A to rows 2 and beyond for each additional line.

Option Explicit
Sub f()
Dim ChatFileNm
Dim FF As Long
Dim destination As Range
Dim ctr As Long
Dim ln$

ChatFileNm = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), >*.txt", Title:="Select Chat File To Be Opened")
If ChatFileNm = False Then Exit Sub
Set destination = Range("A1")
FF = FreeFile
Open ChatFileNm For Input As FF
Do Until EOF(FF)
    Line Input #FF, ln
    'Write the line in to the destination
    destination.Offset(ctr).Value = ln
    'Increment the counter
    ctr = ctr + 1
Loop
'Release the lock on the file
Close FF

End Sub

Alternatively, build the entire text string from the file, and use the Split function with Chr(10) as your delimiter:

Option Explicit
Sub f()
Dim ChatFileNm
Dim FF As Long
Dim destination As Range
Dim ln$, txt$

ChatFileNm = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), >*.txt", Title:="Select Chat File To Be Opened")
If ChatFileNm = False Then Exit Sub
Set destination = Range("A1")
FF = FreeFile
Open ChatFileNm For Input As FF
Do Until EOF(FF)
    Line Input #FF, ln
    'Write the line in to the destination
    txt = txt & ln
Loop
'Release the lock on the file
Close FF

'Write to the sheet:
Dim lines
lines = Split(txt, Chr(10))
Range("A1").Resize(Ubound(lines)+1).Value = Application.Transpose(lines)

End Sub