Can you connect to a SQLite database using Power BI ?

Yes, you can.

Power BI is the Microsoft product for business intelligence. SQLite is well known for its use in mobile platforms and embedded apps.

There is no native way of connecting to SQLite using Power BI, but you could use the ODBC driver which was described in an earlier post.

Read here:
ODBC Drivers for SQLite:

Read here:
Creating ODBC DSN for SQLite3:
https://hodentekmsss.blogspot.com/2018/04/creating-odbc-dsn-for-connecting-to.html

Connecting to SQLite3 using Power BI is easy. Here are the steps.

Launch Power BI and open the Get Data menu as shown.

In the list in the Other clic on ODBC.

Highlight the DSN, ODBCSQLite and Click OK. 

Change it to Windows (this may not be necessary) and click Connect.

The From ODBC window is displayed. Click on Advanced Options to display the pane where you can write a SQL Statement.

It goes through from processing and since there is no table by that name, it returns an error.

Now change the SQL Statement to the following:

Select * from Users

After some processing it get connected.

Now you can find the table details in Navigator as shown.

Power BI does not show the tables and write a query you need to know the objects you created in SQLite database.

Are there ODBC drivers for SQLite databases?

Yes. There are ODBC drivers.

Please go to this site  to download your ODBC driver for SQLite.

The current version are as described in my previous post. Download the one appropriate for you use:

LiteODBC_00

I downloaded the one shown here,

LiteODBC_0

You double click the executable show above to begin the installation.

Follow the wizard. Here are some screens shown for your guidance.

LiteODBC_1

LiteODBC_2

I chose the default folder.

LiteODBC_3

LiteODBC_4

As you may know Windows OS has two ODBC Data Source (x32) and (x64). You can verify that you find the driver in the (x64) version as shown here:

LiteODBC_5

Now you start using SQLite.

In the Install directory (in my case: C:\Program Files\SQLite ODBC Driver for Win64), you can find the ODBC Drivers for two versions,

SQLite 2.8.17 

 SQLite 3.22.0 .

How do you create database 'objects' in SQLite using command line?

In this post,

You will create a SQLite database and persist in your file system.
You will find the directory where the SQLite database is saved.
You will relaunch SQLite3 and access the persisted SQLite database file.
You will create a table.
You will test for tables in your computer/folder.
You will populate the table with some values.
You will run a SELECT query to find the table contents.

I created a desktop shortcut from the file location of sqlite3.exe in one of my folders and launched it. It opens with a command prompt, sqlite>.

The version is at the beginning of the prompt as shown.
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
Creating a database is easy. As shown here it creates a database (file) called 'aloha'.
---------
sqlite> .open aloha
sqlite>
-----------
It creates the database file ("aloha") in the directory which can be seen by the next command. The command .databases without any space after the C: prompt.
------------
sqlite> .databases
main: C:\Users\Owner\Desktop\Blog2017\MSSS2017\SQLite3_DBS\aloha
sqlite>
-------------------
Creating a table is easy as well. The following creates a table 'flower' in the database.
-----------------
sqlite> CREATE TABLE flower ( Id integer PRIMARY KEY, Ename varchar(20), Fname varchar(20));
-------------
Just check if you have any tables using .tables command as shown.
------------------------------------
sqlite> .tables
flower
---------------
Now exit sqlite3.
Launch sqlite3 again.

We will access the saved (persisted) database 'aloha'. Note that double quotes ("") spawns an error as shown. Filename within single quote ('') opens the database
----------------
sqlite> .open "C:\Users\Owner\Desktop\Blog2017\MSSS2017\SQLite3_DBS\aloha"
Error: unable to open database "C:UsersOwnerDesktopBlog2017MSSS2017SQLite3_DBSloha": unable to open database file
sqlite> .open 'C:\Users\Owner\Desktop\Blog2017\MSSS2017\SQLite3_DBS\aloha'
sqlite>
--------------------------------------
Populating the table is also SQL based.
-------------------
sqlite> INSERT INTO flower Values ('1','Hyacinth', 'Jasinth')
   ...> ;
--------------
Now you can run a SELECT query as shown.
--------------
sqlite> SELECT * FROM flower;
1|Hyacinth|Jasinth
sqlite>
-------------------------------

It is continued in the next post tomorrow.