Trying to Read Entire Message History on Iphone and It Wont Load
Here's how yous tin can access your entire iMessage history on your Mac
A guide on how to create a data-science-friendly file with your iMessage history.
If you use the Messages app on your Apple tree figurer and so y'all probably have connected your Apple-Id to that computer in society to send and receive iMessages beyond all your Apple devices (iPhone, iPad, computer). When using the Messages app, you can see all your history of messages on that device and maybe (similar me) you have wondered where exactly is that data stored? Tin can you access information technology in a format that is piece of cake to clarify? Wouldn't it be great to exist able to run across how many letters you accept sent per twenty-four hour period? Or read the first message you have always sent to your friends?
Well you're in luck! Your Apple calculator stores that message history right within your grasp in a subconscious folder on your hard drive!
This is a somehow technical guide for extracting all the iMessage information in your computer'due south hard drive and putting them in an analysis-friendly file.
Note: this arroyo only works for Macs (laptops and desktops) and only gets your iMessage history. Light-green bubble messages unfortunately are non captured.
If you just want to see the skilful stuff you tin can notice the notebook with the minimal lawmaking to extract and set the data here .
Where'south the information
The iMessage history that powers your Letters app is stored in a database file in your computer's hard bulldoze, in a subconscious binder named Library which, in turn, is in your username folder. Yous can usually find your username binder on the side bar of the finder.
Hidden folders are folders that by default don't appear to the user, normally holding files that a casual user doesn't take to interact with, such as system-related files. You can make the subconscious folders announced past simultaneously pressing the Command, the Shift and the dot keys: "Command+Shift+.". If for some reason that doesn't work, yous can also open the Terminal app and simply blazon the post-obit.
defaults write com.apple.finder AppleShowAllFiles Aye Once that works, you tin get and notice the Messages folder, (which contains the chat.db database) within the Library folder as shown in the prototype below.
What'southward a database?
A very elementary way to sympathise what a database file is is to think of it a a folder that contains a agglomeration of excel-like tables. Much like larger or enterprise-form databases yous tin connect and access the data in the database in a diversity of ways.
Using Python and pandas
In this tutorial, I'thousand using Python and the amazing pandas module to connect to the database, explore the tables and data it holds and then read that data from the appropriate tables.
connection code
import sqlite3
import pandas as pd # substitute username with your username
conn = sqlite3.connect('/Users/username/Library/Letters/chat.db') # connect to the database
cur = conn.cursor() # get the names of the tables in the database
cur.execute(" select name from sqlite_master where blazon = 'table' ") for name in cur.fetchall():
impress(proper noun)
In a higher place we connect to the database and explore what tables are in there. I constitute that there are a few tables in the database including i called message and others names conversation, handle and attachment. Let's explore the message table because that's the ane that sounds nigh promising to hold our iMessages. I do that by transferring the tabular array into a pandas dataframe, a type of file that is much easier to explore and dispense for data analyis projects.
# become the 10 entries of the bulletin table using pandas
letters = pd.read_sql_query("select * from message limit x", conn) Getting the message text and phone number
We hitting bingo! The bulletin tabular array indeed seems to hold all the saved iMessages. Information technology has a text field with the actual sent or received message, a appointment field (more on that beneath) and a handle id. Afterwards a little exploration I institute that the handle_id is a code for each phone number or Apple-id that you take had a conversation with. In society to map the handle_id back to the Apple-id we tin use a table in the database (accordingly) named handle and bring together on handle_id.
# get the handles to apple-id mapping table
handles = pd.read_sql_query("select * from handle", conn) # and join to the messages, on handle_id messages.rename(columns={'ROWID' : 'message_id'}, inplace = True) handles.rename(columns={'id' : 'phone_number', 'ROWID': 'handle_id'}, inplace = Truthful) merge_leve_1 = temp = pd.merge(messages[['text', 'handle_id', 'appointment','is_sent', 'message_id']], handles[['handle_id', 'phone_number']], on ='handle_id', how='left')
Adding a conversation id
Similarly, the bulletin table also includes a chat_id that maps each message back to unique chat. This tin can be useful when doing assay on chats with multiple people in them. We tin can get the chat_id of each message by joining the bulletin table with the (again, accordingly named) chat_message_join table on message_id.
# get the chat to bulletin mapping
chat_message_joins = pd.read_sql_query("select * from chat_message_join", conn) # and join back to the merge_level_1 tabular array
df_messages = pd.merge(merge_level_1, chat_message_joins[['chat_id', 'message_id']], on = 'message_id', how='left')
Getting the date
The message table besides includes a date column and this was a little tricky for me to decode since it isn't exactly in any format that is widely used in the industry. Moreover, the way that this column is recorded is a little different in newer version of Mac Os X compared to older ones.
C redit to this stackoverflow page that helped me figure this out.
In Mac Bone X versions earlier High Sierra (which is version 10.thirteen and released in September 2017), the date column is an epoch type but, unlike the standard of counting the seconds from 1970–01–01, it is counting the seconds from 2001–01–01. In guild to convert that type into a information field we can actually comprehend we tin can utilize a command while querying the message table to create a new field (we will call it date_utc, since it is giving a UTC timezone engagement equally a outcome) based on the date field.
# convert 2001-01-01 epoch time into a timestamp
# Mac OS X versions before High Sierra
datetime(message.date + strftime("%s", "2001-01-01") ,"unixepoch","localtime") # how to apply that in the SQL query
messages = pd.read_sql_query("select *, datetime(message.appointment + strftime("%s", "2001-01-01") ,"unixepoch","localtime") as date_uct from message", conn)
In Mac OS X Loftier Sierra and above, it'southward the aforementioned thing only the date format is now much more granular: it is in nano-second level. So now we need to divide by 1,000,000,000 before we apply the same code snippet we applied in a higher place.
# catechumen 2001-01-01 epoch time into a timestamp
# Mac Os X versions after High Sierra
datetime(message.date/1000000000 + strftime("%s", "2001-01-01") ,"unixepoch","localtime") # how to utilize that in the SQL query
messages = pd.read_sql_query("select *, datetime(message.appointment/1000000000 + strftime("%southward", "2001-01-01") ,"unixepoch","localtime") as date_uct from message", conn)
Putting it all together
Yous can find the notebook here with all the code in lodge for yous to excerpt your iMessages from your laptop and get-go analyzing!
It should only accept a few minutes and past the end of it y'all should have a bones history of your iMessage data that includes the phone number (or email), the text, a unique chat for each unique group of people you had a chat with and the timestamp (in UTC timezone) of each bulletin sent.
You can actually detect more data in the database such as details if the message was delivered and read also as attachments. I'grand non touching on those attributes on this mail service.
This post as well but instructs on how to get iMessage data from your Apple tree reckoner. If y'all have whatever pointers on how you tin extract your iMessage history from Apple mobile devices (iPhone & iPad) let me know in the comments.
Happy reading your messages!
Was this helpful? Let me know if things were not clear.
Source: https://towardsdatascience.com/heres-how-you-can-access-your-entire-imessage-history-on-your-mac-f8878276c6e9
0 Response to "Trying to Read Entire Message History on Iphone and It Wont Load"
Post a Comment