Export WhatsApp Chat History to Excel Using Python

Last Updated : 19 Jan, 2023

In this article, we will discuss how to export a specific user's chats to an Excel sheet. To export the chats, we will use several Python modules and libraries.

In the Excel file, we will create four columns: Date, Time, Name, and Message. We'll create these columns through Pandas, export all the chat details to their respective columns, and use Pushbullets to retrieve the data using the API key.

Module needed:

Pandas: Pandas is an open-source library that is built on top of NumPy library. It is a Python package that offers various data structures and operations for manipulating numerical data and time series.

pip install pandas

pushbullet: It allows you to send push notifications to Android and iOS devices.

pip install pushbullet.py == 0.9.1

openpyxl: It is a Python library using which one can perform multiple operations on excel files like reading, writing, arithmetic operations and plotting graphs. Let’s see how to perform different arithmetic operations using openpyxl.

pip install openpyxl

Step-by-step implementation:

Step 1: Set up a Pushbullet account on your PC and Phone

Step 2: Install the Pushbullet app on your phone. Log in using the same email address that you used to log in to your PC.

Step 3: After creating an account on both the computer and the phone.

Navigate to the Devices option in the top left corner of your PC. And then select Add a device, and add your phone. When you add your phone, the name of your phone will be shown there.

Step 4: Now export the specific user's conversations.

Follow these steps:

Go to your phone's WhatsApp app.
Select the user whose conversation you wish to export by clicking on his or her name.
Now, in the upper right corner, click on the three dots.
Select Export chats from the menu that appears when you click on more.
After clicking on Export chats, a list of apps will appear, and you must select Pushbullet.
You may now see a text file on Pushbullet.com on your PC that contains the chats.

Step 5: Now, get the API key from Pushbullet.com

Open Pushbullet.com on your PC and Go to Settings Then scroll down and click on Access Tokens. Click on Create Access Token and copy the Token

Step 6: Write the code for export chat into excel.

Using PushBullet, authenticate the key now. An InvalidKeyError is thrown if your key is invalid (the Pushbullet API returns 401).

Syntax: PushBullet(Your_Access_token)

You may obtain all of your past pushes produced by you, which implies all of the files you submitted to Pushbullet, by using get_pushes. However, we want the most recent push, which will appear at index '0' in a list of all pushes. Now that we've received the most recent push, we need to retrieve the URL of our export conversations from Pushbullet, so just give the file_url.

Syntax:

pb.get_pushes() # Get all the Pushes

all_pushes[0] # Get the latest pushes

Create a text file to save all of the chats, and then use the urlretrieve method to retrieve all of the data from the URL. urlretrieve accepts two arguments: the URL and the text file in which to save all of the data.

Syntax: urllib.request.urlretrieve(Chats_URL, Text_file_name)

Open the text file and read all of the data lines by line, using the utf8 encoding to encode the data. It will save the content of the text file in a list format after reading it from the text file. Because index '0' contains some trash text, we must delete it using list slicing.
Run a loop to extract the Date, Time, Name, and Message from the file data using list slicing. Simply add them to a list once they've been extracted.
Finally, use the Pandas library to construct a data frame that will be used to store all of the data on an Excel sheet. pd.DataFrame accepts two arguments: a list of text and the columns to be created on an excel sheet. After that, just use the to_excel method to save them to an excel file.

Below is the full implementation:

Python3

# Import following modules
import urllib.request
import pandas as pd  
from pushbullet import PushBullet  

# Get Access Token from pushbullet.com
Access_token = "Your Access Token"

# Authentication
pb = PushBullet(Access_token)  

# All pushes created by you
all_pushes = pb.get_pushes()  

# Get the latest push
latest_one = all_pushes[0]  

# Fetch the latest file URL link
url = latest_one['file_url']  


# Create a new text file for storing 
# all the chats
Text_file = "All_Chats.txt"  

# Retrieve all the data store into 
# Text file
urllib.request.urlretrieve(url, Text_file)

# Create an empty chat list
chat_list = []  

# Open the Text file in read mode and 
# read all the data
with open(Text_file, mode='r', encoding='utf8') as f:
  
     # Read all the data line-by-line
    data = f.readlines()  

# Excluded the first item of the list
# first items contains some garbage 
# data
final_data_set = data[1:]

# Run a loop and read all the data
# line-by-line
for line in final_data_set:
      # Extract the date, time, name, 
    # message
    date = line.split(",")[0]  
    tim = line.split("-")[0].split(",")[1]  
    name = line.split(":")[1].split("-")[1]  
    message = line.split(":")[2][:-1]  
    
    # Append all the data in a List
    chat_list.append([date, time, name, message])

# Create a dataframe, for storing
# all the data in a excel file
df = pd.DataFrame(chat_list, 
                  columns = ['Date', 'Time',
                             'Name', 'Message'])
df.to_excel("BackUp.xlsx", index = False)