Hi Dennis,
On 2019-10-17 02:11, Harris, Dennis wrote:
I sure this has been asked a 100 times but what is the best practice to get
data from SQLITE to Excel? I would like to have a pivot table that updates
upon open.
Excel and it's leading open source alternative - LibreOffice Calc, both
have build-in DB/XML datasource facilities [*]. In LibreOffice user
interface should be sufficient. For Excel 2003 (the only version I have)
I am using VBA Macro, pasted bellow.
Testing:
1.Install ODBC driver for SQLite
This is the leading OSS driver, developed by Mr.Cristian Werner:
http://www.ch-werner.de/sqliteodbc/
2.Save module text bellow as vb.base somewhere, then import it into your
workbook (Alt-F11, File/Import file) [**]
3.Name a new sheet in your workbook "sqlite". Paste the following 4
lines in column A starting from A1
```
c:\path\to\your\database.sqlite
tables_and_views select name, 'select * from ' || name sql, type from
sqlite_master where type in ('table', 'view') order by name
```
4.Select Cell A3 (containing tables_and_views), press Ctrl+Shift+d.
Expected result is a table (name, sql) placed from A6.
5.In any cell like A6 - which contains name (suitable for a Excel
identifier - sheet-name, QueryTable name, etc) and sql in the adjacent
cell you can press Ctrl-d for table and Ctrl-t from pivot.
Step 4. is optional (just for illustration of what is expected on step
5.). From step 3. only the path placed in A1 is mandatory.
Hope this mess still work in your Excel version :-)
Kind Regards,
Alek
[*] IMHO, spreadsheet applications should be used primarily for
browsing/pivoting of data based on DB/XML datasources, not as
development place, where the user tries with a bunch of fragile formulas
and macros to achieve the same result as of few lines SQL.
[**] If you want these macros to be available in all workbooks, import
them in a hidden workbook placed in the path, specified at
"Tools/Options/General/At startup" (Usually persnal.xls) or something
similar in your version of Excel.
db.bas:
-------
Attribute VB_Name = "db"
Option Explicit
Private Function sheet_db_name(sheet As Excel.Worksheet)
Select Case ActiveSheet.name
Case "sqlite"
sheet_db_name = sheet.range("a1")
Case "mysql-sample"
sheet_db_name = sheet.range("a1")
Case Else
sheet_db_name = ""
End Select
End Function
Private Function connection_string(connection_key As String, Optional
db_name As String) As String
Select Case connection_key
Case "sqlite"
Let connection_string = _
"ODBC;DRIVER={SQLite3 ODBC Driver};DATABASE=" & db_name
Case "dsn-sample"
Let connection_string = _
"ODBC;DSN=sample;Uid=aUsername;Pwd=aPassword"
Case "mysql-sample"
Let connection_string = _
"ODBC;DRIVER={MySQL ODBC 5.1
Driver};SERVER=127.0.0.1;Port=3306;" _
& "DATABASE=" & db_name & ";USER=aPassword;OPTION=3;"
Case Else
Let connection_string = _
"OLEDB;Provider=SQLOLEDB; Server=127.0.0.1,1433; " & _
"User ID=aUsername; Password=aPassword; Initial
Catalog=" & connection_key
End Select
End Function
Private Function sheet_get(name As String) As Excel.Worksheet
Dim sel As Object
Dim sheet As Excel.Worksheet
For Each sel In ActiveWorkbook.Sheets
If sel.name = name Then
Set sheet_get = sel
Exit Function
End If
Next
End Function
Private Sub db_fetch( _
sql As String, connection_key As String, _
range_at As Excel.range, fetch_name As String, _
Optional db_name As String = "", _
Optional as_pivot As Boolean = False _
)
Dim sheet As Excel.Worksheet
Dim db_connection_string As String
Let db_connection_string = connection_string(connection_key, db_name)
Set sheet = range_at.Worksheet
If Not Err Then
If as_pivot Then
Dim cache As Excel.PivotCache
Set cache = ActiveWorkbook.PivotCaches.Add(xlExternal)
With cache
.Connection = db_connection_string
.CommandType = xlCmdSql
.CommandText = sql
End With
Dim pivot As PivotTable
Set pivot = sheet.PivotTables.Add(cache, range_at, fetch_name)
Let pivot.DisplayImmediateItems = True
Else
With sheet.QueryTables.Add(db_connection_string, range_at, sql)
.name = fetch_name
.FieldNames = True
.RowNumbers = False
.MaintainConnection = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.FillAdjacentFormulas = True
Call .Refresh(BackgroundQuery:=False)
End With
End If
End If
End Sub
Private Function querytable_get(sheet As Excel.Worksheet, name As
String) As Excel.QueryTable
Dim query As Excel.QueryTable
For Each query In sheet.QueryTables
If query.name = name Then
Set querytable_get = query
Exit Function
End If
Next
End Function
Private Sub db_fetch_sheet( _
sheet_name As String, sql As String, connection_key As String, _
result_name As String, _
Optional db_name As String = "", _
Optional as_pivot As Boolean = False, _
Optional before As Excel.Worksheet = Nothing _
)
Dim sheet As Excel.Worksheet
Dim query As Excel.QueryTable
Dim range_at As Excel.range
Set query = Nothing
Set sheet = sheet_get(sheet_name)
If sheet Is Nothing Then
If before Is Nothing Then
Set sheet = ActiveWorkbook.Worksheets.Add
Else
Set sheet = ActiveWorkbook.Worksheets.Add(before)
End If
Let sheet.name = sheet_name
Else
Set query = querytable_get(sheet, sheet_name)
End If
If query Is Nothing Then
Set range_at = sheet.range(IIf(as_pivot, "a4", "a1"))
Call db_fetch(sql, connection_key, range_at, result_name,
db_name, as_pivot)
Call sheet.range("a2").Activate
Let ActiveWindow.FreezePanes = True
Else
Let query.CommandText = sql
Call query.Refresh
End If
End Sub
Public Sub db_fetch_invoke_op(Optional as_pivot As Boolean = False)
Dim sel As Excel.range
Dim cell As Excel.range
Dim connection_key As String
Dim db_name As String
Dim csheet As Excel.Worksheet
Set csheet = ActiveSheet
Let connection_key = csheet.name
Let db_name = sheet_db_name(csheet)
Set sel = Selection
For Each cell In sel.Cells
Call db_fetch_sheet(cell.value, cell.Next, connection_key,
cell.value, db_name, as_pivot, csheet)
Next
End Sub
Public Sub db_fetch_invoke()
Attribute db_fetch_invoke.VB_ProcData.VB_Invoke_Func = "d\n14"
Call db_fetch_invoke_op(False)
End Sub
Public Sub db_fetch_invoke_as_pivot()
Attribute db_fetch_invoke_as_pivot.VB_ProcData.VB_Invoke_Func = "t\n14"
Call db_fetch_invoke_op(True)
End Sub
Public Sub db_fetch_invoke2()
Attribute db_fetch_invoke2.VB_ProcData.VB_Invoke_Func = "D\n14"
Dim sheet As Excel.Worksheet
Dim query As Excel.QueryTable
Dim connection_key As String
Dim result As Excel.range
Dim sql_coords As String, result_coords As String
Dim sql As String
Dim result_name As String
Let result_name = ActiveCell
Set sheet = ActiveCell.Worksheet
Let connection_key = sheet.name
Let sql_coords = Chr(64 + ActiveCell.Column) & ActiveCell.row + 1
Let result_coords = Chr(64 + ActiveCell.Column) & ActiveCell.row + 2
Let sql = sheet.range(sql_coords).value
Set result = sheet.range(result_coords)
If result_name = "" Or sql = "" Then Exit Sub
Set query = querytable_get(sheet, result_name)
If query Is Nothing Then
Call db_fetch(sql, connection_key, result, result_name,
sheet_db_name(ActiveSheet))
Else
Let query.CommandText = sql
Call query.Refresh
End If
End Sub
Public Function qmr(s As String, args As Excel.range) As String
Dim c As Excel.range
Dim v As String
qmr = s
For Each c In args
If IsDate(c) Then
v = Format(c, "yyyy-mm-dd")
Else
v = c
End If
qmr = Replace(qmr, "?", v, , 1)
Next
End Function
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
