What connection string to use to read sqlite db from powerpivot using SQLite ODBC driver

This solution came after many hours of research and trial-and-error. Though it came 2 years late, I am putting it up to help others trying to import information to Power Pivot 2013 from SQLite.

Step 1: Install SQLite ODBC Driver from here.

Step 2: Create a DNS by opening Windows' 'ODBC Data Sources Administrator' (you can find it under Windows > Administrative Tools). See here and here for more information. I have tried creating the DNS under both 'User DNS' and 'System DNS' - both work fine with Power Pivot.

Step 3: Open Power Pivot and do the following:

Click 'From other Sources' > 'Others (OLEDB/ODBC)' > Click on 'Build' button >Under 'Provider' tab > Select 'MS OLE DB Provider for ODBC Sources' > In 'Use Data Source Name', select your DNS created in Step 2 and add any other parameters. At this point, you can test the connection and it should say 'Test Connection Succeeded'

Once you click 'OK', you should see the Connection String automatically generated. Mine was: 'Provider=MSDASQL;Persist Security Info=False;DSN=SQLiteTest'.

Follow the next few steps to import your data from SQLite.

You need something like this:

Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Initial Catalog=C:\XXX.db;DSN=SQLite3 Datasource