asked on

Best way to open an SQLite .db file

Hello,

What is the best app for making a .db file as readable as possible?

I use a softphone program called RingCentral Desktop App for PC. The UI it is not my favorite but for the most part, it handles my phone calls and text messaging OK.

Users can view a log of their regular voice phone calls by logging into their RingCentral account online. However, a huge downside of the app, in my opinion, is that there is no way to view/export/download a log of your text messages. It does have a limited SMS search capability but nothing that allows you to specify a date range or search past messages exchanged with more than a single contact at a time.

I've submitted several requests to RingCentral but no solution or workaround has been offered.

I located the file that holds past text messages here:

C:\Users\[username]\AppData\Local\RingCentral\SoftPhone\[phone number]\storage.db

The file size is 6.45 MB.

Out of curiosity I opened the file using Notepad and, as expected, most of the content (6K+ lines) is made up of meaningless symbols. However, two things I did notice are that:

1) the first line begins with "SQLite format 3" (as shown in the following screenshot) which I assume is the language of the database (if a database has a language).

2) interspersed among the symbols, the file does contain all of the outgoing and incoming text messages (in chronological order) in a readable form as shown in the next screenshot:

(Note: the gray areas are there to block out phone numbers, etc.)

So now I am wondering if there is an app/method/workaround which will render the file in a usable form — or at least more usable form.

Thanks

Dave Baldwin

You can download a copy of the SQLite program from here: https://www.sqlite.org/index.html

Shaun Vermaak

Use this GitHub application SQLiteBrowser. Without it, if you install SQLite you still need to attach the database and write an interface to view data.
https://sqlitebrowser.org/

Pavel Celba

I am using open source SQLite Studio https://sqlitestudio.pl/index.rvt which is free and well documented on the web: http://sqlitestudio.one.pl/index.rvt?act=docs

SQLite Studio will show the SQLite database structure (tables, columns, indexes, ...) and allows to manipulate data using SQL to my satisfaction.

David Favor

If you'd just like to brute force convert the entire file to text you can view.

Simple way.

sqlite3 dbfile .dump > dbfile.txt

Open in new window

WeThotUWasAToad

ASKER

Thanks for the responses.

Simple way.
sqlite3 dbfile .dump > dbfile.txt
Open in new window

I like the idea of simply converting the file directly to .txt (and not having to install an app) if that's an option. However, I don't know what to do with the line of code you included (ie do I put it in a cmd prompt window or something like that?).

I made a copy of the .db file and it is located here:

C:\Users\[username]\RingCentral_db\storage.db

Based on that, can you tell me specifically what to do?

Thanks

WeThotUWasAToad

ASKER

I tried this but it did not produce anything:

Dave Baldwin

That command only works if you have the SQLite program installed that I mentioned above.

ASKER CERTIFIED SOLUTION

Joe Winograd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Edwin Hoffer

Hi,

Simply use any free tool to view your SQLite data.

I recommend to use this software:

https://www.freeviewer.org/sqlite/

some great features are:

Allows to Open & Explore Multiple SQLite Files in a Single Pane
Capable Enough to Scan & Open Corrupt SQLite DB Files
Offers an Option to Examine SQLite File via HEX View
Complete Preview of All Data Stored in SQLite DB Table
Preview Deleted Records from SQLite Table in a Separate Tab
Compatible with all version of Microsoft Windows 10, 8.1, 8, 7, XP, Vista. operating system

Thanks

WeThotUWasAToad

ASKER

Thanks for the continued suggestions.

pcelba

I am using open source SQLite Studio https://sqlitestudio.pl/index.rvt which is free and well documented on the web: http://sqlitestudio.one.pl/index.rvt?act=docs

SQLite Studio will show the SQLite database structure (tables, columns, indexes, ...) and allows to manipulate data using SQL to my satisfaction.

I tried this SQLite Studio and it seems fairly straightforward allowing me to see messages and paste them into a spreadsheet — which for me is much more familiar territory than a database.

The messages are listed by phone number but there's a Contacts section which will allow me to connect names with messages.

One thing I could use some help with is the code they use for Created Time & Modified Time. Following is a screenshot of a small section (attached) of the database which, based on messages in the corresponding rows, I know is in chronological order with the most recent at the bottom.

The main black section is selected columns from the database. The blue section to the left shows specific date and time stamps taken from my phone which correspond to the main data.

I'm especially curious to know where how the date and time information for each text message is conveyed. I tried finding camouflaged dates/times in all the columns with numbers but was not successful. Are there special coded conventions you database experts use for specifying dates & times?

Thanks

TextingLog-sample-2018-09-10a.xlsm

Joe Winograd

I'm especially curious to know where how the date and time information for each text message is conveyed. I tried finding camouflaged dates/times in all the columns with numbers but was not successful. Are there special coded conventions you database experts use for specifying dates & times?

Hi Steve,

They are using the so-called Epoch (aka Unix) timestamp, which is the number of seconds that have elapsed since 1-Jan-1970 at 00:00:00 GMT. Here's an online converter for Epoch timestamps:
https://www.freeformatter.com/epoch-timestamp-to-date-converter.html

I tried that and it works.

Also, here's an article that explains how to do it in Excel:
How to convert between date and Unix timestamp in Excel?

I haven't tried that Excel method, but on a quick review of the article, it looks good to me. Regards, Joe

Pavel Celba

You may calculate the human readable date and time in SQLite Studio Query window directly.

SQL command to list all table values is simple: SELECT * FROM YourTableName

To display the date/time you have to use some of the date conversion function, e.g. strftime:

SELECT strftime('%Y/%m/%d %H:%M:%S', CreationTime, 'unixepoch', 'localtime') AS CreatedDT,   
  strftime('%Y/%m/%d %H:%M:%S', LastModifiedTime, 'unixepoch', 'localtime') AS ModificationDT,  * 
FROM YourTableName

Open in new window

More strftime examples are e.g. here: https://www.w3resource.com/sqlite/sqlite-strftime.php
Modifiers ('unixepoch', 'localtime') are described e.g. here: https://sqlite.org/lang_datefunc.html