How to access an SQLite database from Power Query

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:

http://www.ch-werner.de/sqliteodbc/

 

2016-02-01 11_47_35-SQLite ODBC Driver - Internet Explorer

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:

2016-02-01 11_37_38-chrome cookies.xlsx - Excel

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:

2016-02-01 11_43_28-

You will then see a dialog box asking you to select a table.  For our example, select the “cookies” table

2016-02-01 11_56_42-

and – hopefully – you will see a table containing all the cookies from Google Chrome:2016-02-01 12_14_49-cookies - Query Editor

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.

 

 

2 thoughts on “How to access an SQLite database from Power Query”

  1. Hi…I scanned this with VirusTotal and got one hit….do you know if the file is safe?

    “I recommend using the one located here:
    http://www.ch-werner.de/sqliteodbc/

    Scan results from VirusTotal
    File name: sqliteodbc_w64.exe
    Detection ratio: 1 / 61
    Analysis date: 2017-05-06 21:29:02 UTC ( 1 minute ago )

    Thanks, jim

    1. The file is safe. It appears to be matching based on heuristics rather than any actual malware. This is not surprising given its function of allowing access to databases etc.

Leave a Reply

Your email address will not be published. Required fields are marked *