SQLite is an increasingly common database format for both mobile applications and small desktop applications. Many applications on iOS/Android use them and Google/Mozilla use them for their browsers.
Reading an SQLite database using Power Query isn’t difficult but does require a few initial steps.
To begin with, you need to install an ODBC SQLite3 driver.
I recommend using the one located here:
Make sure that you install the correct version – either 32 bit or 64 bit.
It would also be a good idea to close Excel and re-open before going any further.
Now we create an ODBC query, by selecting From Other Sources->From ODBC:
The next step is to specify a connection string.
Here’s an example that will read the Google Chrome Cookies database (assuming you have Chrome installed and specify your username):
DRIVER=SQLite3 ODBC Driver;Database=C:\Users\<your username>\AppData\Local\Google\Chrome\User Data\Default\Cookies;LongNames=0;Timeout=1000;NoTXN=0;
You will then get asked what credentials to use. In our case, we just want the default of no credentials:
You will then see a dialog box asking you to select a table. For our example, select the “cookies” table
and – hopefully – you will see a table containing all the cookies from Google Chrome:
You will note that most of them are encrypted and therefore not immediately available for us to use.
That’s a problem for another day 🙂
Although this example used the Google Chrome Cookies sqlite3 database, this approach should work with minimal change for most sqlite3 databases.