ODBC connector for Sqlite db file
(1.1) By Dave Mausner (dmausner) on 2024-04-24 14:36:12 edited from 1.0 [link] [source]
My client requires a SQL interface between a Sqlite source and Microsoft PowerBI. We received an outrageous quotation over one thousand dollars for a middleware that will be used once per month. Can any correspondent in this forum suggest a less expensive ODBC solution?
(2) By anonymous on 2024-04-24 14:52:38 in reply to 1.1 [link] [source]
Maybe this SQLite3 ODBC driver provided by Christian Werner under a BSD license can solve your problem.
(3) By Aask (AAsk1902) on 2024-04-24 16:01:48 in reply to 2 [link] [source]
Maybe this SQLite3 ODBC driver provided by Christian Werner under a BSD license can solve your problem.
- It is freely downloadable BUT it is statically bound to a version of SQLite (3.43.2).
- If you use this, you do NOT need to download anything from sqlite.org --
unless you want to
- for related purposes such as trying out your SQL scripts.
- use the experimental versions with which you can use your own version of SQLite
If your application is 32-bit, use the 32-bit ODBC version else use the 64-bit version.
(5.1) By Dave Mausner (dmausner) on 2024-04-25 02:14:49 edited from 5.0 in reply to 3 [link] [source]
Thank you, anonymous and Aask1902, for the reference to ch-werner. This ODBC for Windows (64) works very well. Static binding in its current release to 3.43.2 is okay for us.
(4.1) By Aask (AAsk1902) on 2024-04-24 20:04:06 edited from 4.0 in reply to 1.1 [source]
An interim suggestion (until you figure out which ODBC driver to use) for you to consider:
At the command prompt:
"e:\sqlite32\sqlite3.exe" "e:\sqlite32\db\chinook.db" ".headers on" ".excel" "select * from albums;"
The various strings are within double quotes in order to
- allow for long file names
- separate constituents of the whole command.
"e:\sqlite32\sqlite3.exe" = the fully qualified path of the version of the SQLite CLI that you have downloaded.
"e:\sqlite32\db\chinook.db" = the fully qualified name of your database
".headers on" = ensures that column names are on the first row of your results
".excel" = directs SQLite3.exe to open Excel.Application (assuming it is installed) If you do not have Excel installed, use ".mode csv" (to have the output in CSV format AND ".once fully qualified name of output file" You can use the output file as the data source for PowerBI.
"select * from albums;" = your SQL statement - for retrieving data
Assuming that the number of rows and columns in your result are respectively less than 1,048,576 and 16,384, the results of your SQL statement will appear in an Excel worksheet.(If the limits are exceeded, a truncated version appears in the worksheet)
The Excel worksheet
- has the correct datatypes (dates as dates, numbers as numbers etc)
- can serve as the data source for PowerBI
In order to further fine tune the output of your SQL statement, you will need to investigate SQLite's dot commands.
In Excel, use the Format | AutoFit Column Width to re-size the columns and Format | Cells to format cells.
Try it and provide an update here.
(6) By Dave Mausner (dmausner) on 2024-04-25 02:21:19 in reply to 4.1 [link] [source]
Thank you, Aask1902, for the suggestion of the sqlite3 command line using the .excel dot-command. This will be useful in other projects which actually require Excel output. The part I just learned from your example is that the sqlite3 command line will process many SQL and dot-commands. This was not entirely obvious in the official doc. Well-played !
(7.2) By Aask (AAsk1902) on 2024-04-25 07:46:32 edited from 7.1 in reply to 6 [link] [source]
The official documentation serve well as a reference but (in my view) not so well as a learning resource (especially when proficiency in reading C is limited).
Have a look at the following for further insight.
Command Line Shell For SQLite provides a detailed overview of the CLI.
In particular see 3. Special commands to sqlite3 (dot-commands)
To see all the dot commands, including the undocumented ones.
23. Command-line Options provides further details on the configuration of the CLI start-up.
In particular have a look at: -init: you can use this for many of the dot command options, loading extensions, attaching databases etc. etc.: this will standardize your use of the CLI AND, by an large, make routine usage less verbose.
.read and .read|. This provides the option to store scripts in text files. If you are familiar with Windows BAT or CMD files, you can use parameter substitution (%1 to %9) to build, say, SQL statements dynamically.