Re: [sqlite] Excel Pivot Table

[Alek Paunov]

Forgot to mention the most important feature of spreadsheet datasources:

Once you linked your e.g. pivot to a datasource, you always can refresh
it (usually Ritgh Click/Refresh), after a DB data change.

Kind Regards,
Alek
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Excel Pivot Table

[Alek Paunov]

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
.Adju

Re: [sqlite] Excel Pivot Table

[Simon Slavin]

On 17 Oct 2019, at 12:11am, 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?

Use CSV file format as an interchange format.  Save from SQLite as as a .csv 
file, and import that file into Excel.

If you don't want to write your own code to save your data as .csv, you can 
script the SQLite shell tool to do it:


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Excel Pivot Table

[Harris, Dennis]

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.
Several people will use the file so I will save in a shared location.

Thanks in advance for any advice you can give me

Dennis



.





This e-mail sent from the company specified above and any attachment are 
confidential and may be privileged or otherwise protected from disclosure. It 
is solely intended for the person(s) named above. If you are not the intended 
recipient, any reading, use, disclosure, copying or distribution of all or 
parts of this e-mail or associated attachments is strictly prohibited. If you 
are not an intended recipient, please notify the sender immediately by replying 
to this message or by telephone and delete this e-mail and any attachments 
permanently from your system. It is not guaranteed that emails or attachments 
are secure or error or virus free.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users