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

0 votes

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

enter image description here

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.

image

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.

Oct 14 in Others by Kithuzzz
• 20,660 points
77 views

1 answer to this question.

0 votes

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
answered Oct 14 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

Importing Excel files into R, xlsx or xls

Please can someone help me on the ...READ MORE

Jun 13 in Others by Avinash
• 1,260 points
47 views
0 votes
1 answer

Spell check an Excel sheet in VBA

Use this code to check the whole ...READ MORE

answered Nov 19 in Others by narikkadan
• 37,660 points
33 views
0 votes
1 answer

Embedding an excel document into an application with excel online

You have to do the heavy lifting ...READ MORE

answered Oct 2 in Others by narikkadan
• 37,660 points
68 views
0 votes
1 answer

Is there a way to lock cells after editing an excel sheet that's on sharepoint?

The Excel Web version (which, based on ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
91 views
0 votes
1 answer

Send message using excel to whatsapp web reloading

Inputs Range("W_C") = A list of Contacts' telephone ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
926 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
77 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
107 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
454 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
131 views
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30 in Others by narikkadan
• 37,660 points
44 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP